CODEWITHSUNDEEP
mysqlforeign-keys
Mawg
Mawg

Reputation: 40175

Should I be using onDelete=cascade with my foreign keys?

Related question: Foreign key constraints: When to use ON UPDATE and ON DELETE.

We'll take an example, a company table with a user table containing people from theses company

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)

Now, let's suppose that I have multiple companies, each with multiple customers. I make a habit of having a primary auto index key on each table, and using that as a foreign key on child tables.

So, since my company_id is auto generated and guaranteed to be unique, is there any danger in me setting the foreign key company_id in the users table to onDelete=cascade?

Obviously, my GUI has lots of "are you sure that you are certain that you really want to delete this? Action cannot be undone!"

But, if I don't onDelete=cascade, then before I can DELETE FROM companies WHERE company_id=X, I first have to DELETE FROM users WHERE company_id=X, which is what I have been doing until now.

I am considering onDelete=cascade for the first time & just want to be sure that I have grokked it. Deleting dependent rows can get tedious when the dependency tree is multiple levels deep.

Also, since the keys are auto index, they won;t change, so I can't see that I would need onUpdate.

[Update] One answer was concerned about deleting business data. That's just an example from a related question.

Imagine architecture: a single user can have plans of multiple sites, each with multiple buildings, each with multiple floors, each with multiple rooms.

It is a cascading, tree-like, hierarchy. Does it make sense to have onDelete=Cascade there? I think so, but want to hear from those more more knowledgeable

Upvotes: 0

Views: 493

Answers (1)

Beweeted
Beweeted

Reputation: 339

So much of it will depend on your specific use case. Since you are trying to delete the users anyway, and you want it to happen automatically as part of the cleanup it seems like a good candidate for using ON DELETE to me.

I probably wouldn't be deleting these records though. I would be deactivating them, setting the company to inactive. Then ON UPDATE would be a good candidate, cascading the inactive state down to all users for the company.

I would hesitate to do the delete for two reasons:

First, if the company returns, this allows you to restore the pieces you want for faster setup. And less likely to trigger a restore-from-backup if a company is incorrectly deleted.

Second, I assume that the these entities propagate out into other tables too. I wouldn't want to delete a client/suppliers order history just because we no longer have an active relationship. Even if you don't delete records from those other tables, you'll wind up orphaning the companyId/userId likely in those records.

Upvotes: 1

Related Questions