Migrate tables without primary keys

This page explains how to migrate Oracle tables that don't already have primary keys.

Database Migration Service requires that your destination PostgreSQL tables have primary keys. The default Database Migration Service conversion workspace handles this automatically by creating rowid columns for your tables that don't have primary keys.

But if you're using the legacy conversion workspace with the Ora2Pg migration tool, and your Oracle source tables don't have primary keys, then you need to create the keys manually.

The following sections provide options for how to create primary keys for your Oracle source tables.

Create a primary key using existing columns

Your table might already have a logical primary key based on a column or a combination of columns. For example, there might be columns with a unique constraint or index configured. Use these columns to generate a new primary key in the destination PostgreSQL instance.

Create a primary key using the rowid column

Oracle databases use a rowid pseudocolumn to store the location of each row in a table. To migrate Oracle tables that don't have primary keys, add a new rowid column in the destination PostgreSQL database. Database Migration Service populates the column with the corresponding numeric values from the source Oracle rowid pseudocolumn.

To add the column and to set it as the primary key, run the following:

ALTER TABLE TABLE_NAME 
ADD COLUMN rowid bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY;

ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (rowid);

Replace the following:

• TABLE_NAME: The name of the table in which you want to add the column.
• CONSTRAINT_NAME: The identifier for the PRIMARY KEY constraint.

Create a primary key using all columns

If you can't use the options described earlier, and your source Oracle table doesn't have any duplicate rows, then create a primary key using all columns of the table. Make sure that you don't exceed the maximum length of the primary key allowed by your PostgreSQL instance.