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

- [Automatic primary key generation](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/migrate-tables-without-pks.md.txt#about-automated-keygen) explains how
  Database Migration Service handles source tables with missing primary keys.

- [Add missing primary keys manually](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/migrate-tables-without-pks.md.txt#add-keys-manually) shows example queries that you
  can use to add missing primary keys in your converted schema.

## Automatic primary key generation overview

Tables without primary keys don't promise consistent replication.
Database Migration Service only migrates tables that have primary keys.
When you [convert your source code and schema](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/convert-sql.md.txt), Database Migration Service
can automatically create any missing
primary keys by adding `ROWID` columns in the destination tables.

This automated process requires that your source tables:

- Don't already have primary keys.
- Don't have a `UNIQUE` constraint with no nullable columns.
- Don't have a `UNIQUE` index with no nullable columns.

<br />

If your tables don't meet these requirements, you need to manually
add missing primary keys to the tables in the converted PostgreSQL
schema.

## Add missing primary keys manually

To migrate Oracle tables without primary keys, do the following:

1. Use a conversion workspace to convert your source code and schema. See [Convert your source code and schema](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/convert-sql.md.txt).
2. [Connect to your AlloyDB for PostgreSQL cluster with the `psql` client](https://docs.cloud.google.com/alloydb/docs/connect-psql.md.txt).
3. Create the missing primary key constraints for your tables. For more information about primary keys, see [Primary Keys](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS) in the PostgreSQL documentation.

   You can also expand the following sections to see sample SQL commands:

   #### Create primary keys 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 for tables in your source
   database. For example:

   ```sql
   ALTER TABLE TABLE_NAME
   ADD PRIMARY KEY (COLUMN_NAME);
   ```

   Replace the following:
   - <var translate="no">TABLE_NAME</var> with the name of your table that doesn't have a primary key.
   - <var translate="no">COLUMN_NAME</var> with the name of a column with unique constraint or index that you can use as the primary key.

   <br />

   #### Create a primary key using all columns

   If you don't have a pre-existing constraint that could serve as a
   primary key, create primary keys 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.

   When you create a composite primary key this way, you need to explicitly
   list all column names you want to use. It's not possible to use a statement
   to retrieve all column names for this purpose. For example:

   ```sql
   ALTER TABLE TABLE_NAME
   ADD PRIMARY KEY (COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3, ...);
   ```

   Replace the following:
   - <var translate="no">TABLE_NAME</var> with the name of your table that doesn't have a primary key.
   - <var translate="no">COLUMN_NAME_1</var>, <var translate="no">COLUMN_NAME_2</var> etc. with the names of columns you want to use to create the primary key.

   <br />

   #### Create a unique constraint with the `ROWID` pseudocolumn

   Oracle databases use the
   [`ROWID` pseudocolumn](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWID-Pseudocolumn.html) to store
   the location of each row in a table. To migrate Oracle tables
   that don't have primary keys, you can add a `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:

   ```sql
   ALTER TABLE TABLE_NAME ADD COLUMN rowid numeric(33,0) NOT NULL;
   CREATE SEQUENCE TABLE_NAME_rowid_seq INCREMENT BY -1 START WITH -1 OWNED BY TABLE_NAME.rowid;
   ALTER TABLE TABLE_NAME ALTER COLUMN rowid SET DEFAULT nextval('TABLE_NAME_rowid_seq');
   ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_DISPLAY_NAME PRIMARY KEY (rowid);
   ```

   Replace the following:
   - <var translate="no">TABLE_NAME</var> with the name of the table in which you want to add the column.
   - <var translate="no">CONSTRAINT_DISPLAY_NAME</var> with the identifier for the `PRIMARY KEY` constraint.

## What's next

- [Continue working with conversion workspaces](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/work-with-conversion-workspaces.md.txt).
- [Learn more about Gemini-powered conversion features](https://docs.cloud.google.com/database-migration/docs/oracle-to-alloydb/about-conversion-workspaces.md.txt#ai-conversion).