CODEWITHSUNDEEP
sqlsql-serversql-server-2012foreign-keys
mHelpMe
mHelpMe

Reputation: 6668

one to many relationships and delete cascades

I'm using Microsoft SQL Server 2012.

I have three tables, A, M, Z.

Table A has a 1 to many relationship with the table M. It also has a delete cascade on the foreign key

Table Z is a new table and it should also have a 1 to many relationship with table M, again there should be a delete cascade on the foreign key.

I take it my designed is flawed? Because if I delete a record from table A the corresponding records in table M will be deleted but I'm guessing this will break the relationship between table M & Z? Is that correct?

Upvotes: 1

Views: 1458

Answers (1)

Mureinik
Mureinik

Reputation: 311348

If both constraints are defined as on delete cascade, the relationship won't break - the delete will cascade. When you delete from A all the corresponding records of M will be deleted, and for each record in M, all the records in Z will be deleted.

EDIT:
Based on the conversation in the comments, I misunderstood the question. The correct situation is that A is a parent table, and M and Z are both, independently, its children. In this case, deleting from A will delete from both M and Z, cascading down the relevant constraints. Deleting tom M or Z will not affect the other tables.

Upvotes: 1

Related Questions