A basic design question

When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?

Then if there is a lookup table on the many side how should it be set?

I generally set up the PK-FK constraints and write my own DELETE statements rather than rely on CASCADE Delete. That way I always know the flow of how the data will/should be deleted.|||I was thinking that too. But I was also thinking about how SQL Server will overwrite my thinking if the cascade is not set properly.|||

Hi Jack,

I would suggest to set Update to CASCADE, because the change to the lookup table will be cascaded to the child table.

It does not make difference whether Insert is set to CASCADE or NO ACTION.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!