3 ms·
"Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer" Both form
by rosser 14y ago
"Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer"
Both forms of declaring foreign keys are SQL-standard compliant syntax. That MySQL silently ignores the "inline" version is the "wrong" here, not the syntax.
From MySQL's own docs:
"Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications." [1]
[1] http://dev.mysql.com/doc/refman/5.5/en/create-table.html http://dev.mysql.com/doc/refman/5.5/en/create-table.html
- viraptor 14y agoI understand that and don't have issue with what he most likely meant - only what he wrote. In short - MySQL does not always comply with the SQL standard. That's unfortunate / silly / annoying / inconsistent. I completely agree. But I don't agree with "Foreign keys are ignored if you spell them certain, common, ways". This is not very specific. Common to what situation? People being used to it in another project? It's a documented behaviour. He seems to have a problem with the effect, not the cause. (or maybe I just read it that way...)
- jacques_chester 14y ago> It's a documented behaviour. In other contexts, these are sometimes called "Known Bugs".
- josephlord 14y agoIf it doesn't implement part of the SQL spec that is a shame but why if there is part of the statement that isn't understood or processed there is no excuse for not raising an error. That is unacceptable. Documenting it just isn't enough.
- ldh 14y agoI did some brief searching, but didn't come up with anything. Certainly for such a problematic case there is at least a bug report filed requesting that an error is raised?
- chris_wot 14y agoInterestingly, it's not a syntax error.
- mercurial 14y agoI can understand MySQL not being 100% compliant with the SQL standard. What I have trouble understanding is why it can't be bothered to throw an error. Imagine if your programming language silently ignored some constructs? But it seems to correlate with MySQL's philosophy of being good at silently ignoring problems :(