Geek Answers Handbook

Are update cascades used only for denormalized tables?

I'm just reading about cascading updates, and I was wondering if this is only for denormalized tables, for status and type fields? It seems that normalization will eliminate the need for this, but I just wanted to confirm or find out about other useful reasons for cascading updates. thanks!

+4
source share
2 answers

This is necessary and useful for normalized tables in order to ensure the relationship of foreign keys between them. It’s somewhat less likely that you will change the value of the PK field, especially if it is auto_increment , but when that happens, the change cascades down through the normalized FK relationships.

Similarly, ON UPDATE DELETE is useful for cascading row deletion in all your 1:1 FK relationships, which makes it unnecessary to perform multiple deletions from application code. In any case, this can be the cause of errors.

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 combine records from two databases, but this will cause PK collisions. Now you can safely update all PK custid in customers in one of the databases, and all related orders are automatically reconnected with new identifiers.

+5
source

Cascade updates can also be used to maintain referential integrity. This is primarily useful when the primary key of the parent table is not an auto-increment identifier value.

+4
source

More articles:


All Articles