August 14, 2009 at 3:59 pm
We have one parent table:
Document (GID int identity(1,1),ParentName)
and two child tables, both having FOREIGN KEY(Document_GID) REFERENCES Document (GID)
1. DocumentChild1(Document_GID,Child1_name)
2. DocumentChild2(Document_GID,Child2_name)
Steps:
1. Lets say DocumentChild1 table has 'n' documents.
2. Then in a transaction we try to insert entries into DocumentChild2 from DocumentChild1.
Here, we get foreign key violation :
The INSERT statement conflicted with the FOREIGN KEY constraint "DocumentChild2_DOC_GID_FK".
The conflict occurred in database "VOL_ABC", table "Document", column 'GID'.
Also, we don't have any delete operation in Document table. But many insert operation into this table can be going on in parallel.
We saw this error once. Everything works fine when we reran the insert sql.
I am wondering, what could have caused this behavior. Definitely data exists in the Document table else insert into DocumentChild1 would have failed.
Let me know if you need more details.
August 17, 2009 at 6:41 am
You aren't using nolock or SET ISOLATION LEVEL READ UNCOMMITTED are you?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 17, 2009 at 2:11 pm
My transaction isolation level is READ_COMMITTED and yes, I am using nolocks while reading data from DocumentChild1. But, I have verified that no one is changing(Insert/Update/Delete) data in the DocumentChild1 during that time. Also, I had sql profiler enable when we saw this error. The rowcount suggests that the correct number of records being read from DocumentChild1.
Thanks for your help.
August 17, 2009 at 2:41 pm
I am wondering do indexes on the table play part in this FK constraint violation error.
I have a unique index on DocumentChild2(Document_GID,Child2_name).
August 17, 2009 at 2:59 pm
anki_bakshi (8/17/2009)
I am wondering do indexes on the table play part in this FK constraint violation error.I have a unique index on DocumentChild2(Document_GID,Child2_name).
Nope, you'd get a unique index/constraint violation error.
Was the FK on DocumentChild1 created AFTER there was data in the table? If to there could be "bad" data in the table if the constraint was created with the NOCHECK option
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 17, 2009 at 5:44 pm
No, the FK on DocumentChild1 was created at the time of table creation.
Can multiple asynchronous inserts in the Document table can cause this behavior? I mean may be the index page where the concerned GID is located is locked by the Insert sql as it is also inserting into the same page.
August 19, 2009 at 12:14 pm
Update:
We had a live meeting session with microsoft support where they have debugged this behavior. There is no resolution yet, and might be a bug on their side.
October 28, 2009 at 11:57 am
Update: There is a bug in microsoft sql server's Hash Map join plan when it comes to # based temp tables.
To suppress this error(workaround given by Microsoft team): Include order by 1 clause - this will change the join plan.
For eg.
INSERT INTO MAIN_TABLE (DOC_ID_FK_TO_SOME_OTHER_TABLE, ANY_COLUMN)
SELECT DOC_ID_FK_TO_SOME_OTHER_TABLE, ANY_COLUMN
FROM #TEMP_TABLE -- This will sometimes throw FK violation which is not a --valid violation
ORDER BY 1 -- This suppresses the error
October 28, 2009 at 12:01 pm
Interesting. Thanks for posting the resolution even though it came months later.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2010 at 5:39 pm
Here is KB article ( http://support.microsoft.com/kb/977100/ )explaining about the issue we have raised and possible workarounds in case anyone is interested.
April 1, 2013 at 5:19 am
hello sir..can u plz help me .....i don't knw how to solve this conflict ''The INSERT statement conflicted with the FOREIGN KEY constraint "FK_product_details". The conflict occurred in database "catalog", table "dbo.product_details", column 'product_id' ".......
i have 2 tables, product_details and purchase_details.....
CREATE TABLE product_details
(
product_id int identity(1,1) not null,
product_name varchar(500),
product_catagory varchar(100),
CONSTRAINT PK_product_details PRIMARY KEY(product_id)
)
CREATE TABLE purchase_details
(
Purchase_id int not null identity(1,1) ,
product_id int CONSTRAINT FK_product_details FOREIGN KEY REFERENCES product_details(product_id) ,
Product_Name varchar(500),
Product_Cost VARCHAR(500),
Product_Quantity varchar(500),
CONSTRAINT PK_Purchase_details PRIMARY KEY(Purchase_id)
)
when i was try to insert values in 'purchase_details' that error msg displayed.......i dont know wats the mistak there....
April 1, 2013 at 7:58 am
nitha jen (4/1/2013)
hello sir..can u plz help me .....i don't knw how to solve this conflict ''The INSERT statement conflicted with the FOREIGN KEY constraint "FK_product_details". The conflict occurred in database "catalog", table "dbo.product_details", column 'product_id' ".......i have 2 tables, product_details and purchase_details.....
CREATE TABLE product_details
(
product_id int identity(1,1) not null,
product_name varchar(500),
product_catagory varchar(100),
CONSTRAINT PK_product_details PRIMARY KEY(product_id)
)
CREATE TABLE purchase_details
(
Purchase_id int not null identity(1,1) ,
product_id int CONSTRAINT FK_product_details FOREIGN KEY REFERENCES product_details(product_id) ,
Product_Name varchar(500),
Product_Cost VARCHAR(500),
Product_Quantity varchar(500),
CONSTRAINT PK_Purchase_details PRIMARY KEY(Purchase_id)
)
when i was try to insert values in 'purchase_details' that error msg displayed.......i dont know wats the mistak there....
You really should start your own thread instead of jumping onto somebody else's. However, the issue here is you are trying to insert a row into purchase_details and the value in product_id does not exist in your product_details table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply