cancel
Showing results for 
Search instead for 
Did you mean: 
New

MySQL and adding a foreign key constraint

Trying to create a simple child table to check that the foreign key to the parent table exists before the row in inserted.  I get through the syntax correctly, but the constraint is not operative (I should not be able to insert a row in the child table with a foreign key that does not exist in the parent table... but I can)

I searched the documentation; it seems to say foreign key constraints in the "create table" statement are parsed but ignored, so I used the "alter table" to add the constraint.  Still does not provide referential integrity.  What am I missing? 

- Recipe is the parent table, Ingredient is the child
- id in Recipe is the primary key, recipe_id in Ingredient is indexed

Here's my alter statement:

alter table Ingredient add FOREIGN KEY (recipe_id)

        REFERENCES Recipe(id)

        ON DELETE CASCADE 

It executes fine.  But still allows me to insert rows into Ingredient without recipe_id existing in Recipe, instead substituting a zero.  Ingredient.recipe_id is not null.

2 REPLIES 2
Community Manager
Community Manager

Re: MySQL and adding a foreign key constraint

Hey @RDC. Thanks for being part of GoDaddy Community! Honestly, this is a very complex question. I understand it but I'm not sure what would be causing the problem you're having. The only thing I can suggest is providing more information about the type of account you have and maybe the SQL query you're using to insert the data. That might help others provide you with suggestions. Hope you're able to figure this out. 

 

JesseW - GoDaddy | Community Manager | 24/7 support available at x.co/247support | Remember to choose a solution and give kudos.
New

Re: MySQL and adding a foreign key constraint

As they say, the "devil is in the details" which in this case is the documentation.  In the section on creating foreign keys, it says quite clearly, to provide the most cross-platform compatibility, foreign keys are ignored. Statements are checked for syntax when submitted, but otherwise ignored by the SQL parser and do nothing.

I will instead look into triggers to delete the child entries when the parent entry is deleted.