外键约束

秒速五厘米 2022-09-23 12:48 214阅读 0赞

创建一个有外键的表：

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