foreign key constraint not respected

I have recently switched jobs and at this new company we are using MySQL. I don't have any expereince with MySQL, although I have used SQL Server and Oracle for over 4 years now. Now the strange thing I see with MySQL is that it does not seem to resepect some of the basic things like Foreign Key Constraints (meaning a column is a foregin key but i can insert any value here no matter if it's present in the other table where this FK related to). Now I know in SQL Ser开发者_运维问答ver there is this concept of a NOCHECK foriegn key constraint but the guy at new company responsible for MySQL db say that not respecting a FK is a normal thing in MySQL and it does not need to have any special settings (like NOCHECK FK constraint).

I fail to understand that in a database system how can you ensure referential integirty without having these basic checks in place. I am not sure if the local mySQL "expert" know it well or it's just that mySQL really does not respect FK rules. Any thoughts?

Check that your tables are using the InnoDB engine. When using the MyISAM engine (which was the default until recently), foreign keys declarations are not enforced.

MySQL have different DB Engines -

• MyISAM - default, no FK support
• InnoDB - have FK support - but no fulltext search like in MyISAM

On both engines you can create table and try to create FK, but MyISAM will simply ignore it.

Also, make sure foreign keys are being enforced. For some reason they weren't on mine, leading to one week of headache!

Check:

SELECT @@FOREIGN_KEY_CHECKS

Set:

SET FOREIGN_KEY_CHECKS=1