When to use "ON UPDATE CASCADE"
I use "ON DELETE CASCADE" regularly but I never use "ON UPDATE CASCADE" as I am not so sure in what situation it will be useful.
For the sake of discussion let see some code.
CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE );
For "ON DELETE CASCADE", if a parent with an
id is deleted, a record in child with
parent_id = parent.id will be automatically deleted. This should be no problem.
This means that "ON UPDATE CASCADE" will do the same thing when
idof the parent is updated?
If (1) is true, it means that there is no need to use "ON UPDATE CASCADE" if
parent.idis not updatable (or will never be updated) like when it is
AUTO_INCREMENTor always set to be
TIMESTAMP. Is that right?
If (2) is not true, in what other kind of situation should we use "ON UPDATE CASCADE"?
What if I (for some reason) update the
child.parent_idto be something not existing, will it then be automatically deleted?
Well, I know, some of the question above can be test programmically to understand but I want also know if any of this is database vendor dependent or not.
Please shed some light.
It's true that if your primary key is just a identity value auto incremented, you would have no real use for ON UPDATE CASCADE.
However, let's say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that case, ON UPDATE CASCADE would allow you to change the primary key value and any tables that have foreign key references to the value will be changed accordingly.
In reference to #4, if you change the child ID to something that doesn't exist in the parent table (and you have referential integrity), you should get a foreign key error.
Read more... Read less...
Yes, it means that for example if you do
UPDATE parent SET id = 20 WHERE id = 10all children parent_id's of 10 will also be updated to 20
If you don't update the field the foreign key refers to, this setting is not needed
Can't think of any other use.
You can't do that as the foreign key constraint would fail.
I think you've pretty much nailed the points!
If you follow database design best practices and your primary key is never updatable (which I think should always be the case anyway), then you never really need the
ON UPDATE CASCADE clause.
Zed made a good point, that if you use a natural key (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.
This is not the case if you choose to use surrogate (e.g. artifically system-generated) keys as your primary key (which would be my preferred choice in all but the most rare occasions).
So in the end: if your primary key never changes, then you never need the
ON UPDATE CASCADE clause.
A few days ago I've had an issue with triggers, and I've figured out that
ON UPDATE CASCADE can be useful. Take a look at this example (PostgreSQL):
CREATE TABLE club ( key SERIAL PRIMARY KEY, name TEXT UNIQUE ); CREATE TABLE band ( key SERIAL PRIMARY KEY, name TEXT UNIQUE ); CREATE TABLE concert ( key SERIAL PRIMARY KEY, club_name TEXT REFERENCES club(name) ON UPDATE CASCADE, band_name TEXT REFERENCES band(name) ON UPDATE CASCADE, concert_date DATE );
In my issue, I had to define some additional operations (trigger) for updating the concert's table. Those operations had to modify club_name and band_name. I was unable to do it, because of reference. I couldn't modify concert and then deal with club and band tables. I couldn't also do it the other way.
ON UPDATE CASCADE was the key to solve the problem.
My comment is mainly in reference to point #3: under what circumstances is ON UPDATE CASCADE applicable if we're assuming that the parent key is not updateable? Here is one case.
I am dealing with a replication scenario in which multiple satellite databases need to be merged with a master. Each satellite is generating data on the same tables, so merging of the tables to the master leads to violations of the uniqueness constraint. I'm trying to use ON UPDATE CASCADE as part of a solution in which I re-increment the keys during each merge. ON UPDATE CASCADE should simplify this process by automating part of the process.
It's an excellent question, I had the same question yesterday. I thought about this problem, specifically SEARCHED if existed something like "ON UPDATE CASCADE" and fortunately the designers of SQL had also thought about that. I agree with Ted.strauss, and I also commented Noran's case.
When did I use it? Like Ted pointed out, when you are treating several databases at one time, and the modification in one of them, in one table, has any kind of reproduction in what Ted calls "satellite database", can't be kept with the very original ID, and for any reason you have to create a new one, in case you can't update the data on the old one (for example due to permissions, or in case you are searching for fastness in a case that is so ephemeral that doesn't deserve the absolute and utter respect for the total rules of normalization, simply because will be a very short-lived utility)
So, I agree in two points:
(A.) Yes, in many times a better design can avoid it; BUT
(B.) In cases of migrations, replicating databases, or solving emergencies, it's a GREAT TOOL that fortunately was there when I went to search if it existed.