MySQL doesn't support MATCH, INITIALLY, or DEFERRED

[Former user]

MySQL does not support foreign key options of MATCH, INITIALLY, or DEFERRED. See:

https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html, find for 'Important'

MATCH causes later options such as ON UPDATE and ON DELETE to be silently ignored.

So if a user sets MATCH, INIITALLY, or DEFERRED in their ForeignKeyConstraint object, their schema will differ without any warning.

I therefore propose the MySQL dialect throw a CompileError if those options are specified.

[Mike Bayer]

wow headslap, what a nasty behavior

[Mike Bayer]

So I'm torn between CompileError and warning with or without ignoring it. someone who wants to set up MATCH for other backends would otherwise have to do a conditional DDL here, but if someone actually needs MATCH to be significant probably has to do that anyway.

how about CompileError on 0.9 and warning on 0.8? it is possible existing applications are relying upon current behavior.

[Mike Bayer]

well yeah, this is a total crapshow. we have a precedent of ignoring "deferrable" already set up in #2721. But the approach in #2721 is really only valid when someone is using SET FOREIGN_KEY_CHECKS=0.

So i think we need to rethink this entirely. I'd like to invalidate #2721 and give that user an entirely different path for his FK situation. DEFERRABLE and INITIALLY will be let through again, causing MySQL to fail (it fails, I checked). MATCH will totally raise an error. in 0.8, nothing changes but everything warns.

[Mike Bayer]

cf1ac72bca8b0bc28e09cdb4cdf052bcf82e5076 warning + tests + docs in 0.8

ca02882c6a0d66562d86bf55d5449a04825fa354 error + tests + docs in master

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)