sql - 如何在 SQL Server 2008 中添加引用自身的外键？

Question

我在网上的任何地方都没有看到任何清晰、简洁的例子。

使用现有表，如何添加引用该表的外键？例如:

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

);

我将如何编写一个命令来添加一个引用同一个表的外键？我可以在单个 SQL 命令中执行此操作吗？

Answer 1

我将向您展示几种声明这种外键约束的等效方法。 (此答案有意重复，以帮助您识别声明约束的简单模式。)

示例:这就是我们想要的结果:



案例1:保存外键的列已经存在，但外键关系尚未声明/尚未强制执行:



在这种情况下，请运行以下语句:

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

案例2:该表存在，但它还没有外键列:

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

或更简洁地说:

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

案例3:该表尚不存在。

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);

或者，将约束声明为内联，作为表创建的一部分:

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

或者更简洁:

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.)}