A nice way to add unique constraint to existing table for Oracles DB?

There is an existing table with three columns which all form the primary key.

What is the best way to add a unique column to it? Prefferably creating a sequence for it while Im at it.

Answers


You can add an additional column to a table with an ALTER TABLE

ALTER TABLE table_name
  ADD( new_column_name NUMBER UNIQUE );

You can create a new sequence and then create a trigger that populates the new column using that sequence

CREATE SEQUENCE sequence_name;

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.new_column_name := sequence_name.nextval;
END;

If you are using a version of Oracle prior to 11g, your trigger would need to do a SELECT from DUAL in order to populate the :new.new_column_name column rather than doing a direct assignment

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_column_name 
    FROM dual;
END;

Presumably, you'd also want to initialize all the existing rows using the sequence value before you started inserting new rows

UPDATE table_name
   SET new_column_name = sequence_name.nextval
 WHERE new_column_name IS NULL

But it seems very odd to add a new sequence-generated column to an existing table with a composite primary key unless the goal was to use that new column as the primary key. The whole point of having a sequence generated column is so that you have a stable, synthetic primary key that doesn't depend on the actual business data. So it would seem to make much more sense to drop the existing primary key, add the new column, populate the data, declare the new column as the new primary key, and then define a unique constraint on the three columns that comprised the old primary key.


Need Your Help

Point Of Sales - Stuck on arrays

java arrays object

Basically I have multiple classes and I'm trying to get an array of LineItem for each Item that a customer purchases. LineItem includes the UPC, Description, Price, Quantity, SubTotal and Discount ...

java - very fast writing to a file

java file

I get a fast stream of data (objects) and I would like to write it to a file. This is a stand alone process so it doesn't do anything but read the data from a socket parse it to csv and write all ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.