Clever Geek Handbook

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?

+3


source to share


3 answers


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:

Employee table

Case 1: A column containing foreign keys already exists, but the foreign key relationship has not yet been declared / not yet executed:

Employee table without the foreign key constraint declared

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:

Employee table without 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 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

or FOREIGN KEY

with CONSTRAINT <ConstraintName>

. The way I call foreign key constraints is FK_<TableName>_<ColumnName>

. I name the primary key constraints the same way, only with PK

as a prefix instead of FK

. (Natural and other alternative keys will be prefixed with the name AK

.)

+7


source


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.

+9


source


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
0


source






More articles:


All Articles