INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

GO

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

GO

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie

There is a conflict, you say that staffNo can be null, however, PropStaffFK says that it can not, since you have no staff member with an id of null.|||

Thanks for your reply Motley.

So does that mean if you have a foreign key referencing a primary key from another table that it can't be null?

That makes sence that it would be true, however, in one of my tables TblProperty i have the column staffNo and in this column i want to allow null values to indicate that the property hasn't been approved by a memeber of staff (if it has been approved then the member of staff that approved the property, thier staffNo will be inserted in there) . Do you know of any way to do this?

Thanks

Melanie

|||

Here's the problem. In order to set a FK contraint, you need to reference a primary key table (That has a primary key set). You can't set a primary key index on a column that is nullable. Since the Primary key table doesn't have a NULL-value as one of it's values, then the FK table can not have null as one of it's values either. (Actually, it wouldn't really make all that much sense anyhow, because even if there was a null vaue in the PK table, since null represents UNKNOWN, and UNKNOWN=UNKNOWN is always false, it still shouldn't work even if null was allowed in the PK table).

A workaround is to create an entry into the PK table for unassigned values, like "0" or "-1". Then make the column in the FK table, not-nullable, with a default constraint of "0" or "-1".

|||Another option is to remove the primary key on the primary key table, enter a null value in the old PK column, then apply a unique constraint on that column, then reestablish your FK relationship. Of course, if the old PK table column as of type identity, you'll have to remove that since identity columns can not contain null either.|||

Yeah thats the thing, i have the primary key column set as type Identity.

I like the first idea you mentioned, by putting a 0 or -1 in there replacing the null entry.

Could use the identity type below ( seed 0 and increment 1)

IDENTITY (0, 1)

then the null entry will be replaced by '0' by inserting a fake entry at the start.

Thanks for your help Motley i'll go and try this out.

Melanie

|||You can just remove the indentity from the column, insert your record, and add the identity back on the column too, that works too.|||

How would i carry that out?

I mean to add the identity back to the column after inserting a row of data, wouldn't this require me dropping the table which in turn removes the data entered previouly entered?

Could you tell me the way of doing this without dropping the table?

Thank you

Melanie

|||

I use management studio, so while technically you are correct, it hides it all in the background for me (Copying all the data), and if the table isn't too big, it's quick. But as far as I know, you are correct, you must create a new table to reinstate the identity.

|||

Thanks Motley

What do you suggest for me then?

Guess i'll have to go with '0' to replace the null entry.

Melanie

|||

You might try doing the insert by doing a SET IDENTITY_INSERT {Your table} ON then insert the record, then SET IDENTITY_INSERT {Your table} OFF

Insert Query has Conflict with Foreign Key

there have a exception when I trying to run a insert query

the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..

why? and how to resolve it?

thank you

You need to look up foreign keys in books online, but basically it has to do with putting invalid data into a column that references the data in the primary (or unique) key of another table. As an example:

use tempdb
go
create table parent
(
parentId int primary key
)
go
create table child
(
childId int primary key,
parentId int foreign key references parent(parentId)
)
go
insert into child
values (1,1)
go
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__child__parentId__0CBAE877". The conflict occurred in database "tempdb", table "dbo.parent", column 'parentId'.
The statement has been terminated.
go
insert into parent
values (1)
go
insert into child
values (1,1)

Insert procedure in two tables with Foreign Key relation ship

I was wondering how I do to insert values in two tables that are related each other by a FK?

That is the procedure that illustrate what I meant to be.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

As

Begin

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (Selectuser_idfrom userswhere nick=@.master,@.e_mail)

End

Thank you very much.

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginSET NOCOUNT ONDeclare @.useridintINSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)SELECT @.userid = SCOPE_IDENTITY()INSERT INTO friends ([user_id], e_mail)VALUES (@.userid, @.e_mail)SET NOCOUNT OFFEnd

|||

Thanks to reply ndinakar.

The value inserted "@.userid" in friends table, it is not the same just inserted in the users table. This value is determined by a consult in users table, where nick = "@.master", the return of this consult will say which user_id I will insert in friends table.

|||So do you still need to do an INSERT into the users table with the values you receive in the stored proc or are the values just to be used for lookup?|||

The value @.master is for lookup the table users and gets the user_id. The values @.nick, @.fish, @.e_mail, are for create a new user. And is inserted a new record in friends table with user_id equals to the value consulted.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

As

Begin

Declare@.returned_valuenchar(10)

Selectuser_idfrom userswhere nick=@.master

--I garante it will return a unique value. Supose to be called @.returned_value.

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (@.returned_value,@.e_mail)

End

That will result a data base. Which the new user created will be related with the person who added.

|||

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginDeclare @.returned_valueint-- assuming userid is numeric if not I would recommend nvarchar instead of nchar.Select @.returned_value = [user_id]from userswhere nick=@.master--I garante it will return a unique value. Supose to be called @.returned_value.INSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)INSERT INTO friends ([user_id], e_mail)VALUES (@.returned_value,@.e_mail)End

|||Thank you very much indeed. But at last why you would recommend nvarchar instead of nchar?|||

When you use char, the length becomes a fixed size. So even if you send in a value less than the specified length, SQL pads the value with blank spaces to make it the fixed length.

So 'abc' <> 'abc '. Besides it is also a waste of space/memory. If you use varchar (Variable char) it allocates only as much is required, up to the specified length.

|||

On the select statement I mentioned that it will return a unique value, but if it return nothing or more fields?

If don't want waste your time explaining to me, could just give me the word that I look for on the internet? I tried some but I would never find about this problem specifically. And just wondering, do you know if Microsoft pays some one to stay here in this forum answering questions?

Thank you very much indeed.