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.