Support generic ALTER TABLE ... DROP/ALTER CONSTRAINT <name>

[kyleconroy]

Summary

The SQL-standard ALTER TABLE ... DROP CONSTRAINT <name> and ALTER TABLE ... ALTER CONSTRAINT <name> [NOT] ENFORCED forms work uniformly across CHECK / FOREIGN KEY / UNIQUE / PRIMARY KEY constraints. Marino only supports the older DROP CHECK-style spelling (parser/parser.y:3017) and lacks the generic forms.

MySQL version

Introduced in MySQL 8.0.19.

Current state in marino

grep -in 'DROP CONSTRAINT\|AlterTableDropConstraint' parser/parser.y returns no matches. Only ast.AlterTableDropCheck (constraint-type-specific) exists.

Example SQL

Drop:

CREATE TABLE c1 (id INT, CONSTRAINT chk_pos CHECK (id > 0));
ALTER TABLE c1 DROP CONSTRAINT chk_pos;

Alter enforcement:

CREATE TABLE c2 (id INT, CONSTRAINT chk_pos CHECK (id > 0));
ALTER TABLE c2 ALTER CONSTRAINT chk_pos NOT ENFORCED;
ALTER TABLE c2 ALTER CONSTRAINT chk_pos ENFORCED;

Same form for foreign keys and uniques:

ALTER TABLE orders DROP CONSTRAINT fk_customer;
ALTER TABLE products DROP CONSTRAINT uq_sku;

Validation

Both DROP CONSTRAINT and ALTER CONSTRAINT ... NOT ENFORCED run successfully against MySQL 9.2.0 Community.

Notes for the implementer

• Add AlterTableDropConstraint and AlterTableAlterConstraint AST spec types.
• Extend the AlterTableSpec rule with DROP CONSTRAINT Identifier and ALTER CONSTRAINT Identifier EnforcedOrNot.
• Reference: https://dev.mysql.com/doc/refman/9.2/en/alter-table.html