CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Greg Gum
Greg Gum

Reputation: 38049

How to create a foreign key on Id/ParentId

I have a table of items.

Each Item has an Id, a ParentId which is another item, and an ItemTypeId.

Now, I want to create a constraint to ensure that there every Item has a parent.

One way to do this would be to make the ParentId non-nullable. Sounds simple, but the top item of the chain has no parent, so that is not possible.

I cannot make the Root Item have a ParentId of itself, as the UI sometimes tries to do that, and it causes an orphaned item.

So the next idea is create a nullable foreign key. But I actually don't want the Foreign Key to be null unless ItemTypeId = 1.

So, how do I create that?

Upvotes: 2

Views: 1079

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176024

You could use CHECK constraint:

CREATE TABLE tab(
    ID           INT PRIMARY KEY,
    ITEM_ID_TYPE INT NOT NULL,
    PARENT_ID    INT NULL,
    CHECK ((ITEM_ID_TYPE=1 AND PARENT_ID IS NULL) OR PARENT_ID IS NOT NULL),
    CONSTRAINT FK_TAB FOREIGN KEY (PARENT_ID) REFERENCES TAB(ID)
);

DBFiddle Demo

Upvotes: 1

Related Questions