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 |
funkdoc413
Starting Member
2 Posts |
Posted - 2008-12-12 : 10:44:41
|
I'm having trouble deleting a row with a foreign key constraint. This is causing SQL to throw the following error:The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_product_product1". The conflict occurred in database "xxxx", table "dbo.product", column 'fkProductIDThe code looks like this: DELETE FROM productWHERE ID = 263I have done a lot of searching and realize that I could disable the Foreign Key Constraint before the delete and then re-enable, but When I tried that I got an error saying I wasn't allowed to do that. I'm fairly new to SQL, so any help is great help. Just looking for an easy solution to this problem.Thanks in advance to everyone. |
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-12 : 11:19:20
|
When recreating constraint you can set it not to check previous data.ALTER TABLE t2 WITH NOCHECK ADD CONSTRAINT fk_t2_t1 FOREIGN KEY(col1) REFERENCES t1(col1)But it is stopping delete for a reason so ask yourself: 1. Do you need constraint? - or does it need modified?2. Do you need to delete in one table but not the other? - otherwise delete in other table first. |
|
|
funkdoc413
Starting Member
2 Posts |
Posted - 2008-12-12 : 11:24:43
|
quote: Originally posted by darkdusky When recreating constraint you can set it not to check previous data.ALTER TABLE t2 WITH NOCHECK ADD CONSTRAINT fk_t2_t1 FOREIGN KEY(col1) REFERENCES t1(col1)But it is stopping delete for a reason so ask yourself: 1. Do you need constraint? - or does it need modified?2. Do you need to delete in one table but not the other? - otherwise delete in other table first.
1. I do need the constraint and it may not need to be modified, but I read that in order to make that delete, the constraint must be modified before and after the delete. 2. Yes, I only need the delete in the Product table as far as I know. Thanks for the help so far m8, I appreciate your time. |
|
|
|
|
|