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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Delete Row with Foreign Key Constraint Issue

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 'fkProductID

The code looks like this:
DELETE FROM product
WHERE ID = 263

I 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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -