/aurora-dsql

DDL Migrations: Constraint & Structural Operations

Step-by-step migration patterns for constraint changes, primary key modifications, and column transformations.

MUST read overview.md first for destructive operation warnings and the common verify & swap pattern.

ADD CONSTRAINT Migration

Goal: Add a constraint (UNIQUE, CHECK) to an existing table.

Pre-Migration Validation

MUST validate existing data satisfies the new constraint.

-- For UNIQUE constraint: check for duplicates
SELECT target_column, COUNT(*) as cnt FROM target_table
GROUP BY target_column HAVING COUNT(*) > 1 LIMIT 10;
-- MUST ABORT if any duplicates exist

-- For CHECK constraint: validate all rows pass
SELECT COUNT(*) as invalid_count FROM target_table
WHERE NOT (check_condition);
-- MUST ABORT if invalid_count > 0

Migration Steps

Step 1: Create new table with the constraint

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE,  -- Added UNIQUE constraint
  age INTEGER CHECK (age >= 0),  -- Added CHECK constraint
  other_column TEXT
);

Step 2: Copy data

INSERT INTO target_table_new (id, email, age, other_column)
SELECT id, email, age, other_column
FROM target_table;

Step 3: Verify and swap (see Common Pattern)

DROP CONSTRAINT Migration

Goal: Remove a constraint (UNIQUE, CHECK) from a table.

Pre-Migration Validation

-- Identify existing constraints
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'target_table'
   AND constraint_type IN ('UNIQUE', 'CHECK');

Migration Steps

Step 1: Create new table without the constraint

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  email VARCHAR(255),  -- Removed UNIQUE constraint
  other_column TEXT
);

Step 2: Copy data

INSERT INTO target_table_new (id, email, other_column)
SELECT id, email, other_column
FROM target_table;

Step 3: Verify and swap (see Common Pattern)

MODIFY PRIMARY KEY Migration

Goal: Change which column(s) form the primary key.

Pre-Migration Validation

MUST validate new PK column has unique, non-null values.

-- Check for duplicates
SELECT new_pk_column, COUNT(*) as cnt FROM target_table
GROUP BY new_pk_column HAVING COUNT(*) > 1 LIMIT 10;
-- MUST ABORT if any duplicates exist

-- Check for NULLs
SELECT COUNT(*) as null_count FROM target_table
WHERE new_pk_column IS NULL;
-- MUST ABORT if null_count > 0

Migration Steps

Step 1: Create new table with new primary key

CREATE TABLE target_table_new (
  new_pk_column UUID PRIMARY KEY,  -- New PK
  old_pk_column VARCHAR(255),      -- Demoted to regular column
  other_column TEXT
);

Step 2: Copy data

INSERT INTO target_table_new (new_pk_column, old_pk_column, other_column)
SELECT new_pk_column, old_pk_column, other_column
FROM target_table;

Step 3: Verify and swap (see Common Pattern)

Column Transformations (Split/Merge)

Split Column

Goal: Split one column into multiple (e.g., full_name → first_name + last_name).

-- Create new table with split columns
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);

-- Copy with transformation
INSERT INTO target_table_new (id, first_name, last_name)
SELECT id,
       SPLIT_PART(full_name, ' ', 1),
       SUBSTRING(full_name FROM POSITION(' ' IN full_name) + 1)
FROM target_table;

-- Verify, swap, re-index (see Common Pattern)

Merge Columns

Goal: Combine multiple columns into one (e.g., first_name + last_name → display_name).

-- Create new table with merged column
CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  display_name VARCHAR(512)
);

-- Copy with concatenation
INSERT INTO target_table_new (id, display_name)
SELECT id,
       CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))
FROM target_table;

-- Verify, swap, re-index (see Common Pattern)