Are update cascades only used for denormalized tables?

Question

I'm just reading up on update cascading, and I was wondering if this is only for denormalized tables, for status and type fields? Seems like normalization would remove the need for this, but I just wanted to confirm or learn about other useful reasons for update cascading. Thx!

Answer 1

It is necessary and useful for normalized tables, to enforce the foreign key relationships between them. It's somewhat rarer that you would change the value of a PK field, particularly if it's auto_increment, but when it happens, the change cascades down through the normalized FK relationships.

Likewise, ON UPDATE DELETE is useful to cascade row deletions through all your 1:1 FK relationships making it unnecessary to perform multiple deletes from application code. It can be error-prone to do so anyway.

Consider 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 need to merge records from two databases, but this would cause PK collisions. You can now safely update all the PK custid in customers in one of the databases and all associated orders automatically are reassociated with the new ids.