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;