Unanswered: foreing keys referencing same primay key
I have table
A(id int identity(1,1) PRIMARY KEY)
B(id1 int, id2 int, primary key(id1, id2))
I want to set id1 and id2 as foreign keys that would both reference id in table A.
When i do this i cannot enforce Cascade on Delete and Update actions on one of the Foreing Key constraints. I receive the following message:
Unable to create relationship 'FK_B_A1'.
Introducing FOREIGN KEY constraint 'FK_B_A1' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint.
I need to preserve these tables with their structures and, obviously, when i delete a record from A with id = 1, for instance, all records in B that have either id1 = 1 or id2 = 1 need to be deleted.
That is correct - you can create triggers to enforce cascading deletes for this sort of thing. Other people prefer to manage this in stored procs and only allow data manipulation via these, but I prefer the idiot-proofing of triggers myself.