Skip to content

fix(sql): MySQL DROP FOREIGN KEY, FK ordering, default/index normalization #170

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
jasdeepkhalsa merged 2 commits into from
Apr 3, 2026
Merged

fix(sql): MySQL DROP FOREIGN KEY, FK ordering, default/index normalization #170

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
a16020e
fix(sql): MySQL DROP FOREIGN KEY, FK ordering, default/index normaliz…
jasdeepkhalsa Apr 3, 2026
bb67b6b
test(e2e): update baselines for bug-fix SQL generation changes
jasdeepkhalsa Apr 3, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
46 changes: 44 additions & 2 deletions src/DB/Adapters/MySQLAdapter.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -64,11 +64,13 @@ public function getTableSchema(Connection $connection, string $table): array {
$name = $matches[1];
$line = trim($line, ',');
if (Str::startsWith($line, '`')) {
$columns[$name] = $line;
$columns[$name] = $this->normalizeColumnDef($line);
} elseif (Str::startsWith($line, 'CONSTRAINT')) {
$constraints[$name] = $line;
} elseif (Str::startsWith($line, 'PRIMARY KEY')) {
$keys['PRIMARY'] = $line;
} else {
$keys[$name] = $line;
$keys[$name] = $this->normalizeKeyDef($line);
}
}

Expand Down Expand Up @@ -178,4 +180,44 @@ private function normalizeCreateStatement(string $definition): string {
$definition = preg_replace('/\s*SQL\s+SECURITY\s+(?:DEFINER|INVOKER)/i', '', $definition);
return rtrim(trim($definition), ';');
}

/**
* Normalise a column DDL fragment so that two MySQL versions produce
* identical strings for semantically identical columns.
*
* 1. Strip integer display widths removed in MySQL 8.0.17+.
* 2. Canonicalise CURRENT_TIMESTAMP (case + parentheses).
*/
private function normalizeColumnDef(string $def): string {
// Integer display widths: int(11) → int, tinyint(4) → tinyint, etc.
$def = preg_replace(
'/\b(tinyint|smallint|mediumint|int|bigint)\(\d+\)/i',
'$1',
$def
);

// Normalise CURRENT_TIMESTAMP variants (with optional precision).
// current_timestamp() → CURRENT_TIMESTAMP
// current_timestamp(3) → CURRENT_TIMESTAMP(3)
$def = preg_replace_callback(
'/\bcurrent_timestamp(?:\((\d*)\))?/i',
function ($m) {
$precision = $m[1] ?? '';
return ($precision !== '') ? "CURRENT_TIMESTAMP($precision)" : 'CURRENT_TIMESTAMP';
},
$def
);

return $def;
}

/**
* Normalise an index / key DDL fragment.
*
* Strip trailing USING BTREE — it is the default index type and its
* inclusion varies between MySQL versions, causing false positives.
*/
private function normalizeKeyDef(string $def): string {
return preg_replace('/\s+USING BTREE$/i', '', $def);
}
}
6 changes: 6 additions & 0 deletions src/SQLGen/Dialect/AbstractAnsiDialect.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -80,4 +80,10 @@ public function changeColumn(string $table, string $col, string $newDef): string
protected function changeColumnWarning(string $col): string {
return "-- WARNING: column \"$col\" changed; data may be lost.";
}

public function dropConstraint(string $table, string $name, string $schema): string {
$t = $this->quote($table);
$n = $this->quote($name);
return "ALTER TABLE $t DROP CONSTRAINT $n;";
}
}
16 changes: 16 additions & 0 deletions src/SQLGen/Dialect/MySQLDialect.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -17,6 +17,9 @@ public function isMySQLOnly(): bool {

public function dropIndex(string $table, string $key): string {
$t = $this->quote($table);
if ($key === 'PRIMARY') {
return "ALTER TABLE $t DROP PRIMARY KEY;";
}
$k = $this->quote($key);
return "ALTER TABLE $t DROP INDEX $k;";
}
Expand Down Expand Up @@ -48,4 +51,17 @@ public function changeColumn(string $table, string $col, string $newDef): string
// the backtick-quoted name as the first token.
return "ALTER TABLE $t CHANGE $c $newDef;";
}

/**
* MySQL requires DROP FOREIGN KEY for FK constraints.
* Detects the constraint type from the schema DDL fragment.
*/
public function dropConstraint(string $table, string $name, string $schema): string {
$t = $this->quote($table);
$n = $this->quote($name);
if (stripos($schema, 'FOREIGN KEY') !== false) {
return "ALTER TABLE $t DROP FOREIGN KEY $n;";
}
return "ALTER TABLE $t DROP CONSTRAINT $n;";
}
}
10 changes: 10 additions & 0 deletions src/SQLGen/Dialect/SQLDialectInterface.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -61,4 +61,14 @@ public function dropColumn(string $table, string $col): string;
* Returns the complete statement(s) including trailing semicolons.
*/
public function changeColumn(string $table, string $col, string $newDef): string;

/**
* DROP CONSTRAINT statement.
* $schema is the full constraint DDL used to detect the constraint type.
*
* MySQL requires DROP FOREIGN KEY for FK constraints; DROP CONSTRAINT
* only works for CHECK constraints. Postgres and SQLite use the
* standard DROP CONSTRAINT for all types.
*/
public function dropConstraint(string $table, string $name, string $schema): string;
}
8 changes: 5 additions & 3 deletions src/SQLGen/DiffSorter.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -11,6 +11,8 @@ class DiffSorter {
"DropTrigger",
"DropRoutine",

"AlterTableDropConstraint",

"AddTable",

"DeleteData",
Expand All @@ -29,7 +31,6 @@ class DiffSorter {

"AlterTableAddConstraint",
"AlterTableChangeConstraint",
"AlterTableDropConstraint",

"InsertData",
"UpdateData",
Expand All @@ -56,6 +57,9 @@ class DiffSorter {
"AlterView",
"CreateView",

"AlterTableAddConstraint",
"AlterTableChangeConstraint",

"InsertData",
"AddTable",

Expand All @@ -72,8 +76,6 @@ class DiffSorter {
"AlterTableChangeKey",
"AlterTableDropKey",

"AlterTableAddConstraint",
"AlterTableChangeConstraint",
"AlterTableDropConstraint",

"DeleteData",
Expand Down
5 changes: 2 additions & 3 deletions src/SQLGen/DiffToSQL/AlterTableAddConstraintSQL.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -22,9 +22,8 @@ public function getUp(): string {
}

public function getDown(): string {
$t = $this->dialect->quote($this->obj->table);
$name = $this->dialect->quote($this->obj->name);
return "ALTER TABLE $t DROP CONSTRAINT $name;";
$schema = $this->obj->diff->getNewValue();
return $this->dialect->dropConstraint($this->obj->table, $this->obj->name, $schema);
}

}
10 changes: 5 additions & 5 deletions src/SQLGen/DiffToSQL/AlterTableChangeConstraintSQL.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -15,18 +15,18 @@ public function __construct($obj, SQLDialectInterface $dialect = null) {
$this->dialect = $dialect ?? DialectRegistry::get();
}

private function buildChange(string $schema): string {
private function buildChange(string $dropSchema, string $addSchema): string {
$t = $this->dialect->quote($this->obj->table);
$name = $this->dialect->quote($this->obj->name);
return "ALTER TABLE $t DROP CONSTRAINT $name;\nALTER TABLE $t ADD $schema;";
$drop = $this->dialect->dropConstraint($this->obj->table, $this->obj->name, $dropSchema);
return "$drop\nALTER TABLE $t ADD $addSchema;";
}

public function getUp(): string {
return $this->buildChange($this->obj->diff->getNewValue());
return $this->buildChange($this->obj->diff->getOldValue(), $this->obj->diff->getNewValue());
}

public function getDown(): string {
return $this->buildChange($this->obj->diff->getOldValue());
return $this->buildChange($this->obj->diff->getNewValue(), $this->obj->diff->getOldValue());
}

}
5 changes: 2 additions & 3 deletions src/SQLGen/DiffToSQL/AlterTableDropConstraintSQL.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -22,9 +22,8 @@ public function getUp(): string {
"Cannot generate DROP CONSTRAINT for table `{$this->obj->table}`: constraint name is empty"
);
}
$t = $this->dialect->quote($this->obj->table);
$name = $this->dialect->quote($this->obj->name);
return "ALTER TABLE $t DROP CONSTRAINT $name;";
$schema = $this->obj->diff->getOldValue();
return $this->dialect->dropConstraint($this->obj->table, $this->obj->name, $schema);
}

public function getDown(): string {
Expand Down
118 changes: 115 additions & 3 deletions tests/Unit/AlterTableDropConstraintSQLTest.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -5,7 +5,11 @@
use PHPUnit\Framework\TestCase;
use DBDiff\Exceptions\InvalidConstraintException;
use DBDiff\SQLGen\DiffToSQL\AlterTableDropConstraintSQL;
use DBDiff\SQLGen\DiffToSQL\AlterTableAddConstraintSQL;
use DBDiff\SQLGen\DiffToSQL\AlterTableChangeConstraintSQL;
use DBDiff\SQLGen\Dialect\MySQLDialect;
use DBDiff\SQLGen\Dialect\PostgresDialect;
use DBDiff\SQLGen\Dialect\SQLiteDialect;

class AlterTableDropConstraintSQLTest extends TestCase
{
Expand All @@ -18,18 +22,62 @@ private function makeObj(string $table, string $name, $diff = null): object
];
}

public function testGetUpWithValidName(): void
// ── Bug #17: MySQL DROP FOREIGN KEY syntax ─────────────────────────────

public function testMySQLDropForeignKeyUsesCorrectSyntax(): void
{
$diffOp = new \Diff\DiffOp\DiffOpRemove('FOREIGN KEY (`col`) REFERENCES `other`(`id`)');
$fkDef = 'CONSTRAINT `fk_orders_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)';
$diffOp = new \Diff\DiffOp\DiffOpRemove($fkDef);
$obj = $this->makeObj('orders', 'fk_orders_user_id', $diffOp);
$sql = new AlterTableDropConstraintSQL($obj, new MySQLDialect());

$this->assertSame(
'ALTER TABLE `orders` DROP CONSTRAINT `fk_orders_user_id`;',
'ALTER TABLE `orders` DROP FOREIGN KEY `fk_orders_user_id`;',
$sql->getUp()
);
}

public function testMySQLDropNonFKConstraintUsesDropConstraint(): void
{
$checkDef = 'CONSTRAINT `chk_age` CHECK (`age` > 0)';
$diffOp = new \Diff\DiffOp\DiffOpRemove($checkDef);
$obj = $this->makeObj('users', 'chk_age', $diffOp);
$sql = new AlterTableDropConstraintSQL($obj, new MySQLDialect());

$this->assertSame(
'ALTER TABLE `users` DROP CONSTRAINT `chk_age`;',
$sql->getUp()
);
}

public function testPostgresDropFKUsesStandardSyntax(): void
{
$fkDef = 'CONSTRAINT "fk_orders_user_id" FOREIGN KEY ("user_id") REFERENCES "users"("id")';
$diffOp = new \Diff\DiffOp\DiffOpRemove($fkDef);
$obj = $this->makeObj('orders', 'fk_orders_user_id', $diffOp);
$sql = new AlterTableDropConstraintSQL($obj, new PostgresDialect());

$this->assertSame(
'ALTER TABLE "orders" DROP CONSTRAINT "fk_orders_user_id";',
$sql->getUp()
);
}

public function testSQLiteDropFKUsesStandardSyntax(): void
{
$fkDef = 'CONSTRAINT "fk_orders_0" FOREIGN KEY ("user_id") REFERENCES "users" ("id")';
$diffOp = new \Diff\DiffOp\DiffOpRemove($fkDef);
$obj = $this->makeObj('orders', 'fk_orders_0', $diffOp);
$sql = new AlterTableDropConstraintSQL($obj, new SQLiteDialect());

$this->assertSame(
'ALTER TABLE "orders" DROP CONSTRAINT "fk_orders_0";',
$sql->getUp()
);
}

// ── getDown restores constraint ────────────────────────────────────────

public function testGetDownRestoresConstraint(): void
{
$constraint = 'CONSTRAINT `fk_orders_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)';
Expand All @@ -43,6 +91,8 @@ public function testGetDownRestoresConstraint(): void
);
}

// ── Empty / null name validation ───────────────────────────────────────

public function testGetUpThrowsOnEmptyName(): void
{
$diffOp = new \Diff\DiffOp\DiffOpRemove('FOREIGN KEY (`col`) REFERENCES `other`(`id`)');
Expand All @@ -67,4 +117,66 @@ public function testGetUpThrowsOnNullName(): void
$this->expectException(InvalidConstraintException::class);
$sql->getUp();
}

// ── AlterTableAddConstraintSQL: getDown uses dialect ───────────────────

public function testAddConstraintDownMySQLDropsForeignKey(): void
{
$fkDef = 'CONSTRAINT `fk_posts_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)';
$diffOp = new \Diff\DiffOp\DiffOpAdd($fkDef);
$obj = $this->makeObj('posts', 'fk_posts_user', $diffOp);
$sql = new AlterTableAddConstraintSQL($obj, new MySQLDialect());

$this->assertSame(
'ALTER TABLE `posts` DROP FOREIGN KEY `fk_posts_user`;',
$sql->getDown()
);
}

public function testAddConstraintDownPostgresUsesDropConstraint(): void
{
$fkDef = 'CONSTRAINT "fk_posts_user" FOREIGN KEY ("user_id") REFERENCES "users"("id")';
$diffOp = new \Diff\DiffOp\DiffOpAdd($fkDef);
$obj = $this->makeObj('posts', 'fk_posts_user', $diffOp);
$sql = new AlterTableAddConstraintSQL($obj, new PostgresDialect());

$this->assertSame(
'ALTER TABLE "posts" DROP CONSTRAINT "fk_posts_user";',
$sql->getDown()
);
}

// ── AlterTableChangeConstraintSQL ──────────────────────────────────────

public function testChangeConstraintMySQLUsesForeignKeyDrop(): void
{
$oldDef = 'CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)';
$newDef = 'CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE';
$diffOp = new \Diff\DiffOp\DiffOpChange($oldDef, $newDef);
$obj = $this->makeObj('orders', 'fk_orders_user', $diffOp);
$sql = new AlterTableChangeConstraintSQL($obj, new MySQLDialect());

$this->assertSame(
"ALTER TABLE `orders` DROP FOREIGN KEY `fk_orders_user`;\nALTER TABLE `orders` ADD $newDef;",
$sql->getUp()
);
$this->assertSame(
"ALTER TABLE `orders` DROP FOREIGN KEY `fk_orders_user`;\nALTER TABLE `orders` ADD $oldDef;",
$sql->getDown()
);
}

public function testChangeConstraintPostgresUsesDropConstraint(): void
{
$oldDef = 'CONSTRAINT "fk_orders_user" FOREIGN KEY ("user_id") REFERENCES "users"("id")';
$newDef = 'CONSTRAINT "fk_orders_user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE';
$diffOp = new \Diff\DiffOp\DiffOpChange($oldDef, $newDef);
$obj = $this->makeObj('orders', 'fk_orders_user', $diffOp);
$sql = new AlterTableChangeConstraintSQL($obj, new PostgresDialect());

$this->assertSame(
"ALTER TABLE \"orders\" DROP CONSTRAINT \"fk_orders_user\";\nALTER TABLE \"orders\" ADD $newDef;",
$sql->getUp()
);
}
}
Loading
Loading