CODEWITHSUNDEEP
mysqlforeign-keysinnodbforeign-key-relationship
Meisam Mulla
Meisam Mulla

Reputation: 1871

MySQL and Foreign Keys

I have a question regarding Foreign Keys in an InnoDB Table. I have 4 tables in my database:

Signup:

Affiliates:

Plans:

Industries:

SQL I use to add the keys:

CONSTRAINT `FK_signup_industries` FOREIGN KEY (`industryid`) REFERENCES `industries` (`industryid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_affiliates` FOREIGN KEY (`afid`) REFERENCES `affiliates` (`afid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE CASCADE

What my question is:

If I was to delete a plan or an industry on their respective tables would the user in the signup table be deleted? I have tried to find guides on this and they don't explain it very well.

Basically what I need done is that the row in the signup table never to be deleted no matter what. When I use this query:

CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE NO ACTION

I get this error:

Cannot delete a parent row.

Upvotes: 1

Views: 252

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

If you need the row in Signup to stay even if you delete the referenced row in Industries, Affiliates, or Plans, then you have two choices:

  • Do not declare a foreign key constraint. This removes enforcement of referential integrity, but it allows rows to exist in Signup that reference a parent primary key value that no longer exists.

  • Use ON DELETE SET NULL. This allows the row to stay, but the foreign key value that references the now-deleted parent will be changed.*

For more details see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

* The standard SQL specification also defines a rule ON DELETE SET DEFAULT, but InnoDB doesn't support this feature.

Upvotes: 1

Related Questions