Foreign Key Constraint even though there's no constraint

Question

Hello everyone,

I'm currently developing a script so we can use this for a customer upgrade. While doing test runs I'm receiving Foreign Key Constraints even though there are no constraint values.

First the script deletes a stored procedure if it exists else it creates the following SP:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'FillQueryPropertySelect')
BEGIN
	DROP PROCEDURE dbo.FillQueryPropertySelect
END
GO

CREATE PROCEDURE FillQueryPropertySelect
	(@Id				int
	,@SelectId			int
	,@PropertId			int
	,@PropertyGroupId	int
	)
AS

IF NOT EXISTS(SELECT * FROM QueryPropertySelect WHERE Id = @Id)
	INSERT INTO [QueryPropertySelect] (Id, SelectId, PropertId, PropertyGroupId) VALUES (@Id, @SelectId, @PropertId, @PropertyGroupId)
ELSE
	UPDATE QueryPropertySelect
	SET SelectId = @SelectId,
	    PropertId = @PropertId,
	    PropertyGroupId = @PropertyGroupId 
	WHERE Id = @Id
GO

After this is created the script turns the identity insert to on:

SET IDENTITY_INSERT [QueryPropertySelect] ON 

Once this is done, data is entered using the following statement:

EXEC FillQueryPropertySelect	'1419'	,	'123'	,	'230'	,	'9'

There are roughly 235 lines being inserted using the above statement. Even though there are no constraints visible, there are occasional errors like below:

Msg 547, Level 16, State 0, Procedure FillQueryPropertySelect, Line 11
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_QueryPropertySelect_Select'. The conflict occurred in database '<DBName>', table 'Select', column 'Id'.
The statement has been terminated.

I probably overlooked something somewhere in the code but I can't seem to find the problem in the code.
For extra information, below is the foreign key:

Is there anyone who could help me out with this? All help is very welcome and very much appreciated.

Thank you in advance,
Danny

Answer 1

Looks like you are trying to insert a value in child table which has no correspondent value in parent table .. Means , it is not possible to have a child without a father, am i right :-)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Looks like you are trying to insert a value in child table which has no correspondent value in parent table .. Means , it is not possible to have a child without a father, am i right :-)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Actually this is not the case. I've checked every of the 235 values I'm trying to insert into the "QueryPropertySelect" table and they all exist in the "Select" table which the error refers to.

Answer 3

INSERT INTO [QueryPropertySelect] (Id, SelectId, PropertId, PropertyGroupId)

SelectId, PropertyId & PropertyGrouptID are likely FOREIGN KEYs based on naming conventions.

Id is likely the PRIMARY KEY again based on naming.

A FOREIGN KEY must have a matching PRIMARY KEY for a successful INSERT or UPDATE.

According to the error message, SelectId (INSERT or UPDATE attempt) FK does not have a matching PK.

You can find all PRIMARY KEY & FOREIGN KEY constraints with the query in the following blog post:

http://www.sqlusa.com/bestpractices2005/primaryandforeignkeys/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 4

But you force to insert an identity, perhaps that is a problem. 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

But you force to insert an identity, perhaps that is a problem. 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Wouldn't the set identity_insert to on fix this?

The full overview of Foreign and Primary keys on the table:

dbo	QueryPropertySelect	DocumentClassId	FOREIGN KEY
dbo	QueryPropertySelect	Id	        PRIMARY KEY
dbo	QueryPropertySelect	PropertId	FOREIGN KEY
dbo	QueryPropertySelect	PropertId	FOREIGN KEY
dbo	QueryPropertySelect	PropertyGroupId	FOREIGN KEY
dbo	QueryPropertySelect	PropertyGroupId	FOREIGN KEY
dbo	QueryPropertySelect	SelectId	FOREIGN KEY

I've verified if all foreign keys are available in all related tables and they are. There are values in all related 4 tables.

Answer 6

Get the data you are inserting into a dummy table or a temp table and then join it with the Parent Table, the on whose Primary key is referenced in the child table and then query both the tables with outer join to find the orphan records.

select t.* from dummytable t left outer join ParentTable P on t.id=P.id

WHERE P.id is NULL

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 7

It is hard to suggest without seeing the data, disable this FK and insert the value, then try enable it and find the conflicted value....

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 8

PropertyGoupID column that is the FK must be referring to some existing PK values in one other PropertyGoup table

---

Many Thanks & Best Regards, Hua Min

Answer 9

>Wouldn't the set identity_insert to on fix this?

That is not related to FOREIGN KEY constraint. The error message is "loud and clear". You need to INSERT the missing PK record(s) so the attempted FK-s will match.

The SelectId value (one or more) is not valid in your INSERT or UPDATE. Read my first post?

IDENTITY_INSERT article:

http://www.sqlusa.com/bestpractices2005/design/identityinsert/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 10

Get the data you are inserting into a dummy table or a temp table and then join it with the Parent Table, the on whose Primary key is referenced in the child table and then query both the tables with outer join to find the orphan records.

select t.* from dummytable t left outer join ParentTable P on t.id=P.id

WHERE P.id is NULL

---

Thanks and regards, Rishabh , Microsoft Community Contributor

There is indeed data that is not corresponding as you suggested, however this is not on the error lines. To make sure those non-corresponding lines are the issue I'll try to comment the lines out and try the query again.

Give me a few minutes to check this whether this solves the problem.

Answer 11

@danny,

That will only help you to identify the data that is causing the conflict and if you negate the where clause predicate then you would be sure to insert the matching records only. Another way is to drop or disable the constraint which is not a good practice

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 12

Issue has been found, there were non-corresponding rows between the "select" table and the "querypropertyselect" table.

The errors however pointed to rows that did exist, therefor they threw us off with finding a solution. Basically clicking the error in the query result screen had a exec value that was available in all tables, somewhat weird that the error corresponds to the wrong line in the code.
Since we should have inserted those missing records in the "select" table we'll be looking into it why there are missing lines.

Thank you everyone for your replies and the quick help! I'll mark this thread as solved.