cascading updates does not work, leaves inconsistency

Question

cascading updates does not work, leaves inconsistency

Jrmkjrm 20

Hi,

We use a cascading update between two tables, from a header table to a transaction table. (It's convenient for us so please do not discuss this design, even if not "correct" normalized.) It is set as "Enforce for replication" and "Enforce foreign key constraint".

A few time we noticed that a values differ between the two tables. A header number that is replicated to transaction table (it is never <1 and it never changes in header table) is suddenly set to 0 in the transaction table.

How it is possible that the database accept this severe inconsistency? Why isn't the transaction rolled back?

/Jerome

Jrmkjrm 20 Reputation points

2024-01-09T08:58:51.7033333+00:00

Windows Server 2019 Standard (10.0)

version 15.0.2101.7

ALTER TABLE x.trans WITH CHECK ADD CONSTRAINT FK_header FOREIGN KEY(a, b, c)

REFERENCES x.header (a, b, c)

ON UPDATE CASCADE

GO

ALTER TABLE x.trans CHECK CONSTRAINT FK_header
Dan Guzman 9,516 Reputation points

2024-01-09T13:32:11.63+00:00

Cascading DRI will update key values of the transaction table when the related header row key values (a,b,c) are changed. The cascading update does not modify other columns even those that exist in both tables, such as header number. Replication is unrelated to cascading DRI. It seems you may want a trigger to ensure the header number column values are kept in sync in your denormalized design.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2024-01-09T22:50:41.5233333+00:00

To elaborate on what Dan is saying, if you want to replicate non-key columns from header to transaction table, you will to set up a trigger.