I am just reading through on update cascading down, and I'm wondering if this sounds like just for denormalized tables, for status and kind fields? Appears like normalization would remove the requirement for this, however i wanted to verify or find out about other helpful causes of update cascading down. Thx!
It's important and helpful for stabilized tables, to enforce the foreign key associations together. It's somewhat more rare that you'd change the need for a PK area, especially if it's
auto_increment, however when it takes place, the modification cascades lower with the stabilized FK associations.
ON UPDATE DELETE is helpful to cascade row deletions through all of your
1:1 FK associations which makes it unnecessary to do multiple removes from application code. It may be error-vulnerable to achieve this anyway.
Think about the following:
table customers: custid INT NOT NULL PRIMARY KEY, custname VARCHAR(64) NOT NULL table orders: orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, FOREIGN KEY (custid) REFERENCES customers (custid) ON UPDATE CASCADE ON DELETE CASCADE
Suppose you have to merge records from two databases, but this could cause PK collisions. Now you can securely update all of the PK
customers within the databases and all sorts of connected orders instantly are reassociated using the new ids.
Update cascades may also be used to keep referential integrity. This really is mainly helpful once the parent table's primary secret is no autoincremented identity value.