I found today that if you want to have foreign key constraint in mysql db and if you are not choosing 'innodb' engine then it will be ignored by default engine which is 'MyISAM'.
one of explanation of this behaviour is as follow taken from url
When you define a table with the MyISAM storage engine, it accepts
FOREIGN KEY constraints, but it silently throws them away. It parses them, but does not store the constraint in the table metadata, and subsequently cannot enforce the constraint. When you ask to look at the database's idea of the table definition, it knows nothing about that constraint, as you have found out.
The same thing happens with
CHECK constraints (regardless of the storage engine); it parses the syntax and accepts it, but then ignores it.
IMHO, this is a terrible thing for the MySQL product to do. It accepts standard SQL with no error, leaving you with the impression that it's going to support the constraint in the standard way. But it doesn't! Not even
SHOW WARNINGS reveals that MySQL has disregarded the constraint.
If you use the InnoDB storage engine, it does heed the foreign key constraint.
To provide engine type to create table sql syntax provide ENGINE = InnoDB; argument.
Hope it's new thing . Ignore it if you knew earlier.
No comments:
Post a Comment