外键约束

创建一个有外键的表：

USE Accounting

CREATE TABLE Orders
(
   OrderID      int   IDENTITY   NOT NULL
      PRIMARY KEY,
   CustomerNo   int              NOT NULL
      FOREIGN KEY REFERENCES Customers(CustomerNo),
   OrderDate    date             NOT NULL,
   EmployeeID   int              NOT NULL
);

得到全部约束的名称、条件、以及状态信息：

EXEC sp_helpconstraint Orders

在已存在的表中添加一个外键：

ALTER TABLE Orders
   ADD CONSTRAINT FK_EmployeeCreatesOrder
   FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

是一个表自引用：

注：在实际创建这种引用基于标识列的非空字段的自引用约束之前，很关键的一点是再添加外键之前

表中至少有一行。

例：

INSERT INTO Employees
   (
   FirstName,
   LastName,
   Title,
   SSN,
   Salary,
   PriorSalary,
   HireDate,
   ManagerEmpID,
   Department
   )
VALUES
   (
   'Billy Bob',
   'Boson',
   'Head Cook & Bottle Washer',
   '123-45-6789',
   100000,
   80000,
   '1990-01-01',
   1,
   'Cooking and Bottling'
   );

有了初始行后，就可以添加外键了：

ALTER TABLE Employees
   ADD CONSTRAINT FK_EmployeeHasManager
   FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID);

直接创建则不需要在表中添加一行数据：

CREATE TABLE Employees (
   EmployeeID        int   IDENTITY   NOT NULL
      PRIMARY KEY,
   FirstName         varchar (25)     NOT NULL,
   MiddleInitial     char (1)         NULL,
   LastName          varchar (25)     NOT NULL,
   Title             varchar (25)     NOT NULL,
   SSN               varchar (11)     NOT NULL,
   Salary            money            NOT NULL,
   PriorSalary       money            NOT NULL,
   LastRaise AS Salary -PriorSalary,
   HireDate          smalldatetime    NOT NULL,
   TerminationDate   smalldatetime    NULL,
   ManagerEmpID      int              NOT NULL 
      REFERENCES Employees(EmployeeID),
   Department        varchar (25)     NOT NULL
);