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?

Cleome

I guess you meant "delete all the UserStaff entries", not "delete all the UserStaff tables" – Gait

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;

Laundress

So, I just make the foreign key in the UserStaff table "cascade" then, got it! – Cleome

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

Aplomb

Answer 3

It's been a while since I've used this, but here goes (btw, I use Toad for MySql - a great IDE, and it's free too - http://www.toadworld.com/Freeware/ToadforMySQLFreeware/tabid/561/Default.aspx!)

You need to add a Constraint to the User table. If you have an id column (and the corresponding foreign userid key in UserStaff) then the SouceColumn should be id, the destination table UserStaff and the destination column userid. You can then set the OnDelete action to be 'Cascade'

The other options are pretty self-explanatory - Restrict limits values to the values in the source column, Set Null sets the foreign key matches to Null and No Action does, er, nothing.

This stuff is very easy to do via the Toad IDE. I used MySqlAdmin tools for ages but recently discovered Toad (and it has diff and compare tools too!).

Sigurd

Answer 4

The ON DELETE CASCADE is specified on the foreign key in the UserStaff table. For additional info on foreign keys the MySQL documentation has a number of examples. The User table does not have a foreign key pointing to UserStaff, so it will not be affected by changes to the UserStaff table.

Shag

Answer 5

The easiest way might be to make two quick tables and try it out. But since you didn't I can tell you that the outcome will be that it work the way that you want to.

When you have a table User and a table UserStaff were a field in UserStaff uses a foreign key to reference a field in User; then if you delete a record from UserStaff that will be removed wihtout having any affect on the User table. The other way around will delete all records related to that record.

Short version: A field in UserStaff should reference a field in User with CASCADE

Peaceable