SQL Server批量添加主键脚本求助：AWS数据库迁移需求

Hey there! I totally get the pain of having to add primary keys to dozens of tables mid-migration to AWS—manual work here would be a total drag. Let’s walk through how to automate this with dynamic SQL scripts tailored to the most common AWS database engines (MySQL/Aurora, PostgreSQL/Aurora, SQL Server).

Core Approach #

The plan is straightforward:

1. Identify all tables in your database that don’t already have a primary key.
2. Generate dynamic ALTER TABLE scripts to add a new primary key column and populate it with unique values in one go.
3. Execute the generated scripts (after testing, of course!).

Solution for MySQL/Aurora MySQL #

MySQL’s AUTO_INCREMENT makes this super simple—it’ll handle populating unique values and setting the column as the primary key in a single command.

-- Replace 'your_database_name' with your actual database name
SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
    'ADD COLUMN `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;'
) AS alter_script
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME NOT IN (
      SELECT DISTINCT TABLE_NAME
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      WHERE TABLE_SCHEMA = 'your_database_name'
        AND CONSTRAINT_NAME = 'PRIMARY'
  );

Run this query, and it’ll spit out a list of ALTER TABLE commands. Copy these commands and execute them—MySQL will automatically fill the new id column with unique sequential values and set it as the primary key.

Solution for PostgreSQL/Aurora PostgreSQL #

PostgreSQL uses SERIAL (a shortcut for creating an auto-incrementing sequence tied to the column) to handle this.

-- Adjust 'public' to your target schema if needed
SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
       'ADD COLUMN id SERIAL PRIMARY KEY;' AS alter_script
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
  AND table_name NOT IN (
      SELECT DISTINCT table_name
      FROM information_schema.table_constraints
      WHERE table_schema = 'public'
        AND constraint_type = 'PRIMARY KEY'
  );

Like the MySQL script, this generates all the necessary ALTER TABLE commands. When you run them, PostgreSQL will create the sequence, populate the id column with unique values, and set it as the primary key.

If you prefer UUIDs instead of integers, you can use the uuid-ossp extension (enable it first with CREATE EXTENSION IF NOT EXISTS uuid_ossp;) and modify the script:

SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
       'ADD COLUMN id UUID DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY;' AS alter_script
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
  AND table_name NOT IN (
      SELECT DISTINCT table_name
      FROM information_schema.table_constraints
      WHERE table_schema = 'public'
        AND constraint_type = 'PRIMARY KEY'
  );

Solution for SQL Server/RDS SQL Server #

SQL Server uses the IDENTITY property to auto-populate columns. We’ll also explicitly name the primary key constraint for clarity.

-- Adjust 'dbo' to your target schema if needed
SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' +
       'ADD id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_' + TABLE_NAME + ' PRIMARY KEY;' AS alter_script
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME NOT IN (
      SELECT DISTINCT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = 'dbo'
        AND CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

Run this query to get your ALTER TABLE commands. The IDENTITY(1,1) clause will fill the id column with values starting at 1, incrementing by 1, and the constraint will set it as the primary key.

Critical Notes Before Executing #

• Backup first! Always take a full backup of your database before running bulk schema changes—especially during a migration to AWS.
• Test in staging! Run these scripts against a copy of your database first to ensure they don’t break any application logic or cause unexpected issues.
• Adjust schema/database names in the queries to match your actual setup.

内容的提问来源于stack exchange，提问作者Mayur Randive