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?

I guess you meant "delete all the UserStaff entries", not "delete all the UserStaff tables" — kiedysktos, Jan 30 '17 at 12:38

score 25 · Accepted Answer · edited Nov 30 '11 at 21:50

25

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;

edited Nov 30 '11 at 21:50

Matt Fenwick

48,199
22
128
192

answered Nov 30 '11 at 21:48

Evgeniy Labunskiy

2,012
3
27
45

So, I just make the foreign key in the `UserStaff` table "cascade" then, got it! – Baub Nov 30 '11 at 21:53

score 10 · Answer 2 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 3 · answered Nov 30 '11 at 21:50

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

score 3 · Answer 4 · answered Nov 30 '11 at 21:56

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.

score 2 · Answer 5 · answered Nov 30 '11 at 21:51

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

MySQL Foreign Key On Delete

5 Answers5

Linked

Related