July 17, 2009 at 3:37 pm
Suppose you have a table of People (PersonID, Name, Age, etc) and you want to define which People are Friends, so you create a second table called Friendships. Each friendship contains three columns (FriendshipID, PersonID1, PersonID2). Is this the correct way to go about this? I have spent much time researching and thinking on it to no avail. Problems arise when defining delete specifications on the relationships. Both Person1 and Person2 columns are related to the primary key PersonID in the Persons table. When I try to specify delete specifications on each relationship I get the following error on the second relationship:
"- Unable to create relationship 'FK_Friendships_People2'.
Introducing FOREIGN KEY constraint 'FK_Friendships_People2' on table 'Friendships' 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. See previous errors."
This makes sense because it's worried that if a person was friends with themselves it may try to delete the same row on the second relationship. Looking into this it's rather annoying that SQL Server isn't smart enough to handle this like some other database software. However, pershaps this is just uneducated design? Thanks.
July 17, 2009 at 9:13 pm
NEVER NEVER NEVER create FKs with cascading deletes.
This violates the very purpose of having a foreign key. To this day I am amazed that the possibility still exists.
July 17, 2009 at 9:33 pm
erictyrrell (7/17/2009)
When I try to specify delete specifications on each relationship I get the following error on the second relationship
Hi,
In that scenario, my small suggestion is use the status of the Friendship i.e. adds the status column in the friendship table and updates the status like active and inactive.
July 17, 2009 at 10:58 pm
I understand you are suggesting never to use delete and also update specifications. Could you please expand on the reasons why? Is there any place I can read more on this idea? Is this a suggestion from enterprise patterns and practices? Most importantly, are you then suggesting I make all deletions explicitly in data access code? It seems to me, though I may be mistaken, that automatic delete and update specifications would help with data integrity when records should not exist without other records. Thank you for the advice.
July 18, 2009 at 12:00 pm
Foreign Keys are Constraints in the Relational Model and their purpose is to constrain you from doing something that you should not, because it is probably a mistake. Their purpose is NOT to be some kind of static-trigger that automatically propagates that mistake to other tables.
If you have a FK designed to insure that Master records always have Detail records, deleting the last Detail record, should throw an error because Master records should be deleted with the "Delete Master Record" function and NOT by using the "Delete Detail Record" function on all of its children. Because THAT's probably a mistake.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 18, 2009 at 4:03 pm
NEVER NEVER NEVER create FKs with cascading deletes.
This violates the very purpose of having a foreign key. To this day I am amazed that the possibility still exists.
I also would like to know where you got that because that is DRI(declarative referential integrity). That is what relational purist Chris Date calls if a references b b must exist. I am assuming you know Chris Date does not cover SQL just relational algebra so I am thinking Cascade Delete and Cascade Update must be valid. Check out his last book at a book store near you.
Kind regards,
Gift Peddie
July 19, 2009 at 11:47 am
RBarryYoung (7/18/2009)
Foreign Keys are Constraints in the Relational Model and their purpose is to constrain you from doing something that you should not, because it is probably a mistake. Their purpose is NOT to be some kind of static-trigger that automatically propagates that mistake to other tables.
A foreign key is a declarative constraint. It's wrong to view it as merely some kind of DML "safety" mechanism. The purpose of a constraint is purely and simply to ensure that the database remains consistent at all times.
Now the idea of multiple assignment in a database (updating more than one table simultaneously) is perfectly respectable and in accordance with the relational model. As cited by Gift Peddie, Chris Date, Hugh Darwen and others have asserted that it's an essential feature of the relational model, and I agree with them - for sound and very practical reasons it is virtually indispensible. Unfortunately, in standard SQL the only way to achieve that is through triggers and cascading constraints.
So although I dislike the SQL syntax generally, I think that cascading deletes and updates are an important and needed feature of FOREIGN KEYs in SQL. I certainly don't see any reason to say that they are contrary to the relational model. Quite the reverse.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply