I am trying to figure out relationships and deletion options.
I have two tables,
UserStaff, with a 1:n relationship from
UserStaff (a user can have multiple staff members).
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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Yes, it’s possible. You should make the FK in UserStaff table. In this way:
CREATE TABLE `User` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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;
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).
User is the master table, and
UserStaff is the child table. So, yes, you’ll want to create the foreign key in
ON DELETE CASCADE
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!).
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
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