fix(sql): MySQL DROP FOREIGN KEY, FK ordering, default/index normalization#170
Merged
jasdeepkhalsa merged 2 commits intomasterfrom Apr 3, 2026
Merged
fix(sql): MySQL DROP FOREIGN KEY, FK ordering, default/index normalization#170jasdeepkhalsa merged 2 commits intomasterfrom
jasdeepkhalsa merged 2 commits intomasterfrom
Conversation
…ation - #17: Add dialect->dropConstraint() — MySQL emits DROP FOREIGN KEY for FK constraints, DROP CONSTRAINT for others (Postgres/SQLite use standard syntax) - #41: Reorder DiffSorter so DROP CONSTRAINT runs before DROP TABLE (UP) and ADD CONSTRAINT runs after ADD TABLE (UP) - #66: Normalize integer display widths (int(11)→int) and CURRENT_TIMESTAMP variants in MySQLAdapter::normalizeColumnDef() - #61: Strip trailing USING BTREE from index DDL in normalizeKeyDef() - #91: Store PRIMARY KEY under key name 'PRIMARY'; MySQLDialect::dropIndex() emits DROP PRIMARY KEY when key is 'PRIMARY' Tests: 52 new/updated unit tests across 4 test classes (570 total pass)
Re-record E2E expected outputs to match corrected SQL generation: - MySQL/Dolt: DROP PRIMARY KEY instead of DROP INDEX for PKs (#91) - MySQL/Dolt: DROP FOREIGN KEY instead of DROP CONSTRAINT for FKs (#17) - All dialects: constraint DROPs now sorted before table ops (#41) Verified locally via Podman: MySQL 8.0, PostgreSQL 16, SQLite — all pass.
aa82a65 to
bb67b6b
Compare
|
This was referenced Apr 3, 2026
Closed
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1 participant
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
Fixes 5 bugs related to constraint SQL generation, FK dependency ordering, and MySQL schema normalization.
Bug #17 — MySQL DROP FOREIGN KEY syntax
MySQL doesn't support
DROP CONSTRAINTfor foreign keys — it requiresDROP FOREIGN KEY. AddeddropConstraint($table, $name, $schema)toSQLDialectInterface:FOREIGN KEYin schema DDL → emitsDROP FOREIGN KEY; falls back toDROP CONSTRAINTfor CHECK/UNIQUEDROP CONSTRAINTBug #41 — FK dependency ordering
DiffSorterreordered so:AlterTableDropConstraintruns beforeDropTable(drop FKs before dropping referenced tables)Bug #66 — Default value normalization
Added
MySQLAdapter::normalizeColumnDef():int(11)→int,tinyint(4)→tinyint, etc.) — removed in MySQL 8.0.17+, their presence varies between versionsCURRENT_TIMESTAMPvariants (case, empty parens, ON UPDATE clause)Bug #61 — Index false positives
Added
MySQLAdapter::normalizeKeyDef():USING BTREE— default index type whose inclusion varies between MySQL versions, causing phantom diffsBug #91 — Index SQL generation (PRIMARY KEY)
MySQLAdapter::getTableSchema()now storesPRIMARY KEYlines under key name'PRIMARY'instead of extracting the first column nameMySQLDialect::dropIndex()emitsALTER TABLE t DROP PRIMARY KEY;when key is'PRIMARY'Tests
DiffSorterConstraintOrderTest.php— 4 tests for FK orderingMySQLAdapterNormalizationTest.php— 25 data-driven tests for column/key normalizationIndexSQLGenerationTest.php— 13 tests for index SQL across all 3 dialectsAlterTableDropConstraintSQLTest.php— expanded from 4 to 14 testsFiles Changed (12)
Source (9):
src/SQLGen/Dialect/SQLDialectInterface.php— newdropConstraint()methodsrc/SQLGen/Dialect/MySQLDialect.php—dropConstraint()+dropIndex('PRIMARY')supportsrc/SQLGen/Dialect/AbstractAnsiDialect.php—dropConstraint()default implementationsrc/SQLGen/DiffToSQL/AlterTableDropConstraintSQL.php— usesdialect->dropConstraint()src/SQLGen/DiffToSQL/AlterTableAddConstraintSQL.php— usesdialect->dropConstraint()src/SQLGen/DiffToSQL/AlterTableChangeConstraintSQL.php— usesdialect->dropConstraint()src/SQLGen/DiffSorter.php— reordered constraint operationssrc/DB/Adapters/MySQLAdapter.php— normalization methods + PRIMARY KEY parsingTests (3 new + 1 updated):
tests/Unit/AlterTableDropConstraintSQLTest.phptests/Unit/DiffSorterConstraintOrderTest.phptests/Unit/IndexSQLGenerationTest.phptests/Unit/MySQLAdapterNormalizationTest.php