Setting the file. One moment.
Subchapter 63.25
references/mysql-migrations/ddl-constraints.mdMarkdown2 KBView on GitHub
Part of MySQL to DSQL DDL Migration. See Common Verify & Swap Pattern for the shared migration end-pattern.
MySQL syntax:
ALTER TABLE table_name MODIFY COLUMN column_name datatype NOT NULL;
ALTER TABLE table_name MODIFY COLUMN column_name datatype NULL;DSQL: MUST use Table Recreation Pattern.
SELECT COUNT(*) as null_count FROM target_table
WHERE target_column IS NULL;
-- MUST ABORT if null_count > 0, or plan to provide default valuesCREATE TABLE target_table_new (
id UUID PRIMARY KEY,
target_column VARCHAR(255) NOT NULL, -- Changed from nullable
other_column TEXT
);INSERT INTO target_table_new (id, target_column, other_column)
SELECT id, COALESCE(target_column, 'default_value'), other_column
FROM target_table;Step 3: Verify and swap (see Common Pattern)
MySQL syntax:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;DSQL: MUST use Table Recreation Pattern.
CREATE TABLE target_table_new (
id UUID PRIMARY KEY,
status VARCHAR(50) DEFAULT 'pending', -- Added default
other_column TEXT
);INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;Step 3: Verify and swap (see Common Pattern)
CREATE TABLE target_table_new (
id UUID PRIMARY KEY,
status VARCHAR(50), -- Removed DEFAULT
other_column TEXT
);INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;Step 3: Verify and swap (see Common Pattern)