1

Why when I try to run this query in MySQL, it gives me a syntax error. I'd like to drop a foreign key only if exists

ALTER TABLE
    `messaggi`
DROP CONSTRAINT IF EXISTS
    `messaggi_ibfk_1`,
DROP CONSTRAINT IF EXISTS
    `messaggi_ibfk_2`;
pietro bacigalupi
  • 41
  • 6
  • Your title implied SQLServer (Microsoft) And then your question suggest MySQL but you also add mariaDB. Bit of a guessing game you have made for us here – RiggsFolly Feb 24 '21 at 17:36
  • https://stackoverflow.com/questions/17161496/drop-foreign-key-only-if-it-exists – RiggsFolly Feb 24 '21 at 17:40

1 Answers1

1

When in doubt, refer to the reference documentation on syntax:

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

| DROP {CHECK | CONSTRAINT} symbol

It only supports the symbol, i.e. the constraint name, directly after DROP CONSTRAINT. It does not support an optional IF EXISTS clause after DROP CONSTRAINT.

That's just how the SQL parser code for MySQL is implemented. It does not support any syntax you think of, it only supports syntax they implemented. Lots of software works this way. :-)

This enhancement to SQL syntax was requested in 2004, but declined: https://bugs.mysql.com/bug.php?id=5746

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828