Add a primary key column to an old table

Question

So I have a table with some 50+ rows. And currently this tables doesnot have any primary key/ID column in it. Now if I have to add a primary key column, its not allowing me to because already data are present in the table and there is as such no unique column or combination of columns. Can anyone suggest me how to add a primary column to an existing table with data in it.

Answer 1

In your case when the table due to missing PK definition suffers some duplicated records, you may do a stepwise recovery.

In the first step you disables the creation of the new duplicated rows.

Let's assume your PK candidate columns are col1, col2 such as in the example below:

CREATE TABLE test_pk as
SELECT 'A' col1, 1 col2 FROM dual UNION ALL
SELECT 'A' col1, 2 col2 FROM dual UNION ALL
SELECT 'B' col1, 1 col2 FROM dual UNION ALL
SELECT 'B' col1, 1 col2 FROM dual;

You can not define the PK because of the existing duplications

ALTER table test_pk  ADD CONSTRAINT my_pk UNIQUE (col1, col2);
-- ORA-02299: cannot validate (xxx.MY_PK) - duplicate keys found

But you can crete an index on the PK columns and set up a constraint in the state ENABLE NOVALIDATE.

This will tolerate existing duplicates, but reject the new once.

CREATE INDEX my_pk_idx ON test_pk(col1, col2);

ALTER TABLE test_pk
ADD CONSTRAINT my_pk UNIQUE (col1,col2) USING INDEX my_pk_idx
ENABLE NOVALIDATE;

Now you may insert new unique rows ...

INSERT INTO test_pk (col1, col2) VALUES ('A', 3);
-- OK

... but you can't create new duplications:

INSERT INTO test_pk (col1, col2) VALUES ('A', 1);
-- ORA-00001: unique constraint (xxx.MY_PK) violated

Later in the second step you may decide to clenup the table and VALIDATE the constraint, which will make a perfect primary key as expected:

-- cleanup
DELETE FROM TEST_PK 
WHERE col1 = 'B' AND col2 = 1 AND rownum = 1;

ALTER TABLE test_pk MODIFY CONSTRAINT my_pk ENABLE VALIDATE;