Add a primary key column to an old table-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

(From 121.) You can add a new auto-incremented surrogate key to a table with either:

alter table t
  add ( t_id integer generated by default as identity );

create sequence s;
alter table t
  add ( t_id integer default s.nextval );

These set the value for all the existing rows. So may take a while on large tables!

You should also look to add a unique constraint on the business keys too though. Do to that, take the steps Marmite Bomber suggests.