12.1

Add primary key to existing table in Oracle

Posted by Ronald on January 25, 2017  /   Posted in Oracle

In addition to a previous article on How to add a primary key to an existing table in MySQL, I tried the same in Oracle as since version 12c this feature is also supported.

SELECT * FROM x1;

COL1
----------
34
43
11
65
89

We have a table with COL1 filled with some values. Now if we add the primary key with

ALTER TABLE x1 
  ADD (col2 NUMERIC(2)) generated BY DEFAULT ON NULL AS IDENTITY;

Table altered.

we see the following values in table x1

SELECT * FROM x1;

COL1       COL2
---------- ----------
34         1
43         2
11         3
65         4
89         5

COL2 is automatically filled with a unique sequence id number.

Note: Bear in mind that adding this kind of primary key to a huge table can take a considerable amount of time.

Now cleaning up gave a rather surprising feedback:

drop table x1;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [91872],  [],...

We ran into unpublished bug 18744247 – ORA-30667 ON DROPPING COLUMN WITH ‘DEFAULT ON NULL’ ATTRIBUTE. The problem has something to do with the fact that the auto increment column is not available in the dictionary tables.

When trying to reproduce on one of our Oracle database on docker with same version 12.1.0.2, but with a different PSU level, the problem didn’t occur.

So if you will run into this problem really depends on your database version. The problem should be solved in Oracle 12.2 but interim patches are available for databases with recent PSU updates installed.

 

 

 

^ Back to Top