Foreign key linked with primary key in same table

Question

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

https://i.sstatic.net/kXiMS.png

Answer 1

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.