Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.04 seconds. Powered By: Snitz Forums 2000