MySQL Foreign Key On Delete

Question

I am trying to figure out relationships and deletion options.

I have two tables, User and UserStaff, with a 1:n relationship from User to UserStaff (a user can have multiple staff members).

When my User is deleted, I want to delete all of the UserStaff tables associated with that User. When my UserStaff is deleted, I don't want anything to happen to User. I understand that this is a cascading relationship, but I'm not sure which way.

i.e. Do I select the existing foreign key in my UserStaff table and make it cascading, or do I create a new foreign key in User and set that to cascading?

Answer 1

Yes, it's possible. You should make the FK in UserStaff table. In this way:

User Table

CREATE TABLE `User` (   `Id` int(11) NOT NULL AUTO_INCREMENT,   `Name` varchar(255) DEFAULT NULL,   PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

UserStaff Table

CREATE TABLE `UserStaff` (   `Id` int(11) NOT NULL AUTO_INCREMENT,   `UserId` int(11) NOT NULL DEFAULT '0',   PRIMARY KEY (`Id`),   KEY `UserId` (`UserId`),   CONSTRAINT `UserStaff_ibfk_1`      FOREIGN KEY (`UserId`)      REFERENCES `User` (`Id`)      ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Answer 2

From Wikipedia:

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

Here, User is the master table, and UserStaff is the child table. So, yes, you'll want to create the foreign key in UserStaff, with ON DELETE CASCADE