Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-03-06 : 01:45:43
|
| I am in the process of normalizing data from a partially unormalized database in order to make it scalable. My questions is this.My mothertable TBLUSERDETAILS has a primary key constraint on userIDboth columns in tblhotlist have a constraint to userID on tbluserdetailsTBLHOTLISTFK_USERIDFK_HOTUSERIDWhen I delete the row containing userID '500' for example, I would like all rows containg '500' in either column in TBLHOTLIST automatically deleted as well, to avoid orphaned data.I have cascading deletes set on, but I get the following errorMicrosoft][ODBC SQL Server Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__tblHotLis__HotUs__39E294A9'. The conflict occurred in database 'ratemybody', table 'tblHotList', column 'HotUserID'Am I missing something ??Thanks, MIke |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-03-06 : 21:12:08
|
| I'm not sure I'm totally following you. I think this is more or less what you are describing.CREATE TABLE Parent ( UserID int identity PRIMARY KEY)CREATE TABLE Children ( Father int REFERENCES Parent (UserID) ON DELETE CASCADE Mother int REFERENCES Parent (UserID) ON DELETE CASCADE)Now, I don't have anything to test my theory at the moment, but I believe SQL Server is finding a conflict because it's trying to do both deletes on one table, and there is all sorts of fun that could happen depending on the order of the 2 separate deletes. (especially with triggers involved, this example is too simplistic to really show anything nasty that might happen)In order to get around this, create an INSTEAD OF trigger for the delete action. Inside of it, you can use delete statements on the children table, and then finish deleting the entries from the parent table.----------------------"O Theos mou! Echo ten labrida en te mou kephale!"Edited by - Lavos on 03/06/2002 21:13:09 |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-06 : 23:44:21
|
| yes that is exactly what is happening. Thanks, I will read up on triggers, I have no previous experience with them...I dont quite understand what could go wrong if it cascaded to delete both referential constraints? At least not in this example.Is there a simpler way? Thanks, Mike |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-03-07 : 19:55:31
|
| As I said, this case wouldn't be a problem :)I can't think of one right now, but I know in the back of my head that there is some situation where it would pop up to bite you in the rear. As such, it's probably safer to just disallow any ambiguity rather than let the server behave in undefined ways. (I don't personally agree with this in all cases, but it's not my call)Triggers aren't too bad, you can do a search in the articles section for some decent introductory stuff. (There are two intro articles IIRC.)(Heck, if I'm remembering right, there is an article section on triggers and the intro's should be right on top somewhere.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
|
|
|
|
constraints question