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)
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.
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.
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.