How to add a foreign key referring to itself in SQL Server 2008?
I have not seen any clear and eloquent examples of this on the Internet.
With an existing table, how do I add a foreign key that refers to that table? For example:
CREATE TABLE dbo.Projects(
ProjectsID INT IDENTITY(1,1) PRIMARY KEY,
Name varchar(50)
);
How do I write a command to add a foreign key that refers to the same table? Can I do this in one SQL command?
I'll show you some equivalent ways to declare such a foreign key constraint. (This answer is deliberately repeated to help you recognize simple constraint declaration patterns.)
Example: This is what we would like to receive:
Case 1: A column containing foreign keys already exists, but the foreign key relationship has not yet been declared / not yet executed:
In this case, run this statement:
ALTER TABLE Employee
ADD FOREIGN KEY (ManagerId) REFERENCES Employee (Id);
Case 2: The table exists, but it doesn't have a foreign key column yet:
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 an inline constraint 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)
);
<sub> PS regarding name constraints: Prior to the previous version of this answer, more meaningful SQL examples contained suggestions
CONSTRAINT <ConstraintName>
for providing unique names to foreign key constraints.After @ypercube's comment, I decided to drop those suggestions from the examples for two reasons: Constraint naming is an orthogonal problem ( that is, an installation-independent restriction is in place. And having naming allows us to focus on actually adding constraints.In short, to name a limitation, precede any mention, eg.
PRIMARY KEY
,REFERENCES
orFOREIGN KEY
withCONSTRAINT <ConstraintName>
. The way I call foreign key constraints isFK_<TableName>_<ColumnName>
. I name the primary key constraints the same way, only withPK
as a prefix instead ofFK
. (Natural and other alternative keys will be prefixed with the nameAK
.)
You can add column and constraint in one operation
ALTER TABLE dbo.Projects ADD
parentId INT NULL,
CONSTRAINT FK FOREIGN KEY(parentid) REFERENCES dbo.Projects
You can optionally include the PK column in parentheses after the link table name, but it is not needed here.
If the table already exists: Let's say you don't already have a column to store this data. If you do, skip this step.
ALTER TABLE [dbo].[project]
ADD [fkProjectsId] INT;
GO
ALTER TABLE [dbo].[projects]
ADD CONSTRAINT [FK_Projects_ProjectsId] FOREIGN KEY ([fkProjectsId]) REFERENCES [dbo].[Projects] ([ProjectsID])
GO