When to use ON UPDATE CASCADE

Question

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 id of the parent is updated  If  1  is true  it means that there is no need to use  ON UPDATE CASCADE  if parent id is not updatable  or will never be updated  like when it is AUTO INCREMENT or 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 id to 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

User · Answer

The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted  The following are permitted actions   NO ACTION  CASCADE  SET NULL  and SET DEFAULT  Delete actions of rows in the parent table If you delete one or more rows in the parent table  you can set one of the following actions   ON DELETE NO ACTION  SQL Server raises an error and rolls back the delete action on the row in the parent table  ON DELETE CASCADE  SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table  ON DELETE SET NULL  SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted  To execute this action  the foreign key columns must be nullable  ON DELETE SET DEFAULT  SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted  To execute this action  the foreign key columns must have default definitions  Note that a nullable column has a default value of NULL if no default value specified  By default  SQL Server appliesON DELETE NO ACTION if you don   t explicitly specify any action   Update action of rows in the parent table If you update one or more rows in the parent table  you can set one of the following actions   ON UPDATE NO ACTION  SQL Server raises an error and rolls back the update action on the row in the parent table  ON UPDATE CASCADE  SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated  ON UPDATE SET NULL  SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated  Note that the foreign key columns must be nullable for this action to execute  ON UPDATE SET DEFAULT  SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated   FOREIGN KEY  foreign key columns      REFERENCES parent table parent key columns      ON UPDATE  lt action gt       ON DELETE  lt action gt    See the reference tutorial

User · Answer

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

User · Answer

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   Marc

User · Answer

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

User · Answer

Yes  it means that for example if you do UPDATE parent SET id   20 WHERE id   10 all 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

User · Answer

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

User · Answer

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

[sql] When to use "ON UPDATE CASCADE"

Delete actions of rows in the parent table

Update action of rows in the parent table

Examples related to sql

Examples related to foreign-keys

Examples related to foreign-key-relationship