Urgent: Adding a primary key in a table with duplicates.

[hemanr]

Hi again Gurus,

I have a table containing duplicate rows. Can I add a primary key to this table so that I maintain the duplicated rows yet prevent future duplicate inserts???

Thanks!!!

[Zaggy]

I would create the primary key using the novalidate cluase. This will create the constraint, but not ensure that all current values conform to the primary key. Also, subsequent insert will be checked against the primary key. Does this work for you?

Here is a link to the create Table statement:
http://otn.oracle.com/doc/oracle8i_8...6989/ch4d2.htm
I recommend looking at the "constraint_clause" link for the novalidate option.

[bmycroft]

Hi there, novalidate will work BUT you should be aware of the issues.... If you have a column that you treat as a primary key -ie unique- in your applications then are you not opening the door for a huge support issue later on? The big question really is why do you want to use a non-unique column as a primary key? Can you combine the column with anything else to make it unique? Is the column a numeric identifier - if so then have you considered 'massaging' the numbers to make them unique before you apply the primary key?

It would really worry me knowing that data wasnt really unique but that the developers were treating it as such...