Migrate tables without primary keys

Database Migration Service migrates only tables that have primary keys. If your source database includes tables that don't have primary keys, you need to manually create primary key constraints in the converted tables in the destination database after you apply the converted schema.

To migrate SQL Server tables without primary keys, do the following:

  1. Convert all your schema and apply it to the destination database.
  2. Connect to your AlloyDB for PostgreSQL instance with the psql client.
  3. Create the missing primary key constraints for your tables. For more information about primary keys, see 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: 

    ALTER TABLE TABLE_NAME
ADD PRIMARY KEY (COLUMN_NAME);

    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. For example: 

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

    When creating a composite primary key like this, 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.

What's next