Sql-server – Foreign key linked with primary key in same table

foreign-key-relationshipsql-serversql-server-2012

I have a table Categories with columns Id, ParentId (for "subcategories" whom can have any level of nesting) and some other. Using SQL Server 2012 I can't make foreign key in same table, FK_Categories_Categories (Id -> ParentId).

Error message is

'Categories' table
– Unable to create relationship 'FK_Categories_Categories'. The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Categories_Categories". The conflict occurred in database "pokupaykadb", table "dbo.Categories", column 'Id'.

That needs for cascade deletion of subcategories. What solution can be? It's desirable to be a some property, like cascade deletion from another table by foreign key

http://i.stack.imgur.com/kXiMS.png

Best Solution

If there are orphaned records that does not meet your constraint criteria - delete them before creating the foreign key.

Usually there are few records which doesn't go by the new constraint and that the DBMS doesn't allow to create the constraint.

Related Question