Auto Increment

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.

 

 

 

Add primary key to existing table in MySQL

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

One of my colleague sysadmins wanted to set up replication for his WordPress hosting databases. Some of his customers were using rather old WP plug-ins using tables that actually didn’t have a Primary Key (PK) defined on it. And since having (a sort of) PK is one of the first requirements for setting up replication he asked me how to solve this in an easy way.

The table has only 1 column with values:

mysql> select * from x1;
 +------+
 | col1 |
 +------+
 | 1    |
 | 2    |
 | 3    |
 | 4    |
 | 1    |
 | 2    |
 | 3    |
 | 4    |
 +------+
 8 rows in set (0.00 sec)

In MySQL adding a PK can be done with the AUTO INCREMENT option so we don’t have to worry about any sequence or serial number

ALTER TABLE x1
  ADD col2 INT PRIMARY KEY auto_increment; 

Query OK, 8 rows affected (0.16 sec)
Records: 8 Duplicates: 0 Warnings: 0

As we see from the feedback, 8 rows are affected. That means in this case that every record has it’s col2 value defaulted to a next unique sequence number:

mysql> select * from x1;
+------+------+
| col1 | col2 |
+------+------+
|   1  |   1  |
|   2  |   2  |
|   3  |   3  |
|   4  |   4  |
|   1  |   5  |
|   2  |   6  |
|   3  |   7  |
|   4  |   8  |
+------+------+
8 rows in set (0.00 sec)

Col2 has now unique values for each row and a new PK is defined on the table:

mysql> desc x1;
 +-------+---------+------+-----+---------+----------------+
 | Field | Type    | Null | Key | Default | Extra          |
 +-------+---------+------+-----+---------+----------------+
 | col1; | int(11) | YES  |     | NULL    |                |
 | col2; | int(11) | NO   | PRI | NULL    | auto_increment |
 +-------+---------+------+-----+---------+----------------+
 

Bear in mind that executing this action on a huge table can take a long time so first run this action on a test environment to determine the execution time.

Adding a PK with auto increment in Oracle is described in Add primary key to existing table in Oracle.

^ Back to Top