SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Delete Row with Foreign Key Constraint Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funkdoc413
Starting Member

2 Posts

Posted - 12/12/2008 :  10:44:41  Show Profile  Reply with Quote
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 - 12/12/2008 :  11:19:20  Show Profile  Reply with Quote
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 - 12/12/2008 :  11:24:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000