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.

Having data in a table does not forbid adding a PK or UK. What may be the problem is that if any combination you want has duplicates. If that is the case, then you need to decide what to do with the duplicates, either modify a columns there, or remove duplicates. When your eliminates the duplicates, you can surely add a PK/UK — gsalem, Jan 07 '22 at 12:57
Or add a new column to use as a synthetic primary key, as an identity column (if you're on a recent version of Oracle) or set via a sequence and trigger. It depends what the full requirement is, what the PK will be used for - if another table already has data that now needs to be treated as an FK to this table, you still have a problem with non-unique data... — Alex Poole, Jan 07 '22 at 12:59
If none of your current columns (or combinations) can act as a key, you have to add a new column. Does this answer your question? [Add a auto increment primary key to existing table in oracle](https://stackoverflow.com/questions/11464396/add-a-auto-increment-primary-key-to-existing-table-in-oracle) — derpirscher, Jan 07 '22 at 13:10

score 1 · Answer 1 · answered Jan 07 '22 at 13:46

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;

Chris Saxon · Accepted Answer · 2022-03-07T11:47:59.753

(From 12.1) 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 );

Or

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. To do that, take the steps Marmite Bomber suggests.

Add a primary key column to an old table

2 Answers2