copilot-setting sql-ddl.instructions.md

MySQL DDL & Migration Conventions

Rules for schema changes, migration scripts, and stored procedures. Query / JDBC / HQL rules: instructions/sql.instructions.md.

MySQL DDL & Migrations

Schema conventions — apply to every CREATE TABLE / ALTER TABLE, not just stored procedures:

• Tables: InnoDB, utf8mb4, created_at / updated_at timestamps mandatory
• FK: fk_<child>_<parent_col>; RESTRICT default, CASCADE only for dependent children
• Index: idx_<table>_<columns>

Migration safety — every DDL / data-modifying DML script:

• Every up statement has a down / rollback script; never DROP TABLE without explicit sign-off
• Drop a column renamed-then-dropped across two releases; rename a column add-new → dual-write → drop-old — never single-shot
• ADD COLUMN ... NOT NULL DEFAULT <constant> is ALGORITHM=INSTANT on MySQL 8.0.12+ (metadata-only, safe at any size) — append , ALGORITHM=INSTANT so it errors instead of silently rebuilding when INSTANT can't apply (a STORED generated column, ROW_FORMAT=COMPRESSED, FULLTEXT index, >64 prior instant changes, pre-8.0.29 non-trailing position). Do NOT pre-emptively reach for the nullable → backfill → MODIFY COLUMN ... NOT NULL dance: that final MODIFY is the genuinely expensive INPLACE table rebuild — reserve it for the cases INSTANT rejects. MySQL has no ALTER COLUMN ... SET NOT NULL, so MODIFY must re-list the full column definition (DEFAULT / COMMENT / charset) or they are silently dropped
• A large-table ALTER that rebuilds the table uses online schema change (pt-osc / gh-ost) or carries an explicit downtime note; index creation uses ALGORITHM=INPLACE, LOCK=NONE where supported
• Backfill and long UPDATE / DELETE are idempotent and chunked by PK range, committed per chunk (low thousands of rows as a starting point — tune per the batch rule in instructions/sql.instructions.md)
• Idempotency guards: CREATE / DROP TABLE take IF [NOT] EXISTS, but MySQL has none for ADD/DROP COLUMN or CREATE/DROP INDEX — guard those with an information_schema.COLUMNS / STATISTICS existence check (or rely on the migration tool's version tracking)
• Running-app compatibility: new columns nullable or DB-defaulted so an old app instance's INSERT won't fail; drop columns only after every instance stops reading them; realign hbm.xml / DAO with the post-migration schema after deploy