How to add a foreign key referring to itself in SQL Server 2008?

Question

I have not seen any clear, concise examples of this anywhere online.

With an existing table, how do I add a foreign key which references this table? For example:

CREATE TABLE dbo.Projects(
    ProjectsID INT IDENTITY(1,1) PRIMARY KEY,
    Name varchar(50)

);

How would I write a command to add a foreign key which references the same table? Can I do this in a single SQL command?

Solution 1

I'll show you several equivalent ways of declaring such a foreign key constraint. (This answer is intentionally repetitive to help you recognise the simple patterns for declaring constraints.)

Example: This is what we would like to end up with:

---

Case 1: The column holding the foreign keys already exists, but the foreign key relationship has not been declared / is not enforced yet:

In that case, run this statement:

ALTER TABLE Employee
ADD FOREIGN KEY (ManagerId) REFERENCES Employee (Id);

---

Case 2: The table exists, but it does not yet have the foreign key column:

ALTER TABLE Employee
ADD ManagerId INT, -- add the column; everything else is the same as with case 1
    FOREIGN KEY (ManagerId) REFERENCES Employee (Id);

or more succinctly:

ALTER TABLE Employee
ADD ManagerId INT REFERENCES Employee (Id);

---

Case 3: The table does not exist yet.

CREATE TABLE Employee -- create the table; everything else is the same as with case 1
(
    Id INT NOT NULL PRIMARY KEY,
    ManagerId INT
);

ALTER TABLE Employee
ADD FOREIGN KEY (ManagerId) REFERENCES Employee (Id);

or, declare the constraint inline, as part of the table creation:

CREATE TABLE Employee
(
    Id INT NOT NULL PRIMARY KEY,
    ManagerId INT,
    FOREIGN KEY (ManagerId) REFERENCES Employee (Id)
);

or even more succinctly:

CREATE TABLE Employee
(
    Id INT NOT NULL PRIMARY KEY,
    ManagerId INT REFERENCES Employee (Id)
);

---

_{P.S. regarding constraint naming: Up until the previous revision of this answer, the more verbose SQL examples contained CONSTRAINT <ConstraintName> clauses for giving unique names to the foreign key constraints. After a comment by @ypercube I've decided to drop these clauses from the examples, for two reasons: Naming a constraint is an orthogonal issue to (i.e. independent from) putting the constraint in place. And having the naming out of the way allows us to focus on the the actual adding of the constraints.}

_{In short, in order to name a constraint, precede any mention of e.g. PRIMARY KEY, REFERENCES, or FOREIGN KEY with CONSTRAINT <ConstraintName>. The way I name foreign key constraints is <TableName>_FK_<ColumnName>. I name primary key constraints in the same way, only with PK instead of FK. (Natural and other alternate keys would get the name prefix AK.)}

Solution 2

You can add the column and constraint in one operation

ALTER TABLE dbo.Projects ADD 
            parentId INT NULL, 
            CONSTRAINT FK FOREIGN KEY(parentid) REFERENCES dbo.Projects

Optionally you could specify the PK column in brackets after the referenced table name but it is not needed here.