SQL Server 知识汇集11 : 约束2

FOREIGN KEY约束语法

创建 FOREIGN KEY 约束的一般语法如下：

CONSTRAINT fk_constraint_name
FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

下面来详细学习一下这种语法。

首先，在 CONSTRAINT 关键字后指定 FOREIGN KEY 约束名称。约束名称是可选的(不用指定也可以)，因 此可以按如下方式定义 FOREIGN KEY 约束：

FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

在这种情况下，SQL Server将自动为 FOREIGN KEY 约束生成名称。

其次，在 FOREIGN KEY 关键字后面指定括号括起来的逗号分隔外键列的列表。

第三，指定外键引用的父表的名称以及与子表中的列具有链接的逗号分隔列的列表。

FOREIGN KEY约束示例

首先，在 vendor_groups 表中插入一些行：

INSERT INTO procurement.vendor_groups(group_name)
VALUES('第三方供应商'),
('优品供应商'),
('一次性供应商');

其次，将具有供应商组的新供应商插入 vendors 表：

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('ABC Corp', 1);

上面语句按预期工作。

第三，尝试插入 vendor_groups 表中不存在供应商组的新供应商：

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('XYZ Corp',4);

SQL Server发出以下错误：

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group".
The conflict occurred in database "BikeStores", table
"procurement.vendor_groups", column 'group_id'.

在此示例中，由于 FOREIGN KEY 约束， vendor_groups 表中 group_id 列的值为 4 的行不存在。因此 SQL Server拒绝插入并发出错误。

参考操作

外键约束确保了引用完整性。如果父表中存在相应的行，则只能在子表中插入一行。

此外，外键约束用 于在更新或删除父表中的行时定义引用操作，如下所示：

FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;

ON UPDATE 和 ON DELETE 指定在更新和删除父表中的行时将执行的操作。 以下是允许的操作： NO ACTION ， CASCADE ， SET NULL 和 SET DEFAULT 。

删除父表中行的操作

如果删除父表中的一行或多行，则可以设置以下操作之一：

• ON DELETE NO ACTION ：SQL Server引发错误并回滚父表中行的删除操作。

• ON DELETE CASCADE ：SQL Server删除子表中与从父表中删除的行相对应的行。

• ON DELETE SET NULL ：如果删除父表中的相应行，则SQL Server将子表中的行设置为 NULL 。 要执行此操作，外键列必须可为 NULL 。

• ON DELETE SET DEFAULT ：如果删除父表中的相应行，SQL Server会将子表中的行设置为其默认 值。 要执行此操作，外键列必须具 有默认定义。 请注意，如果未指定默认值，则可空列的默认值为 NULL 。

  默认情况下，如果未明确指定任何操作，则SQL Server将应用 ON DELETE NO ACTION 。

更新父表中行的操作

如果更新父表中的一行或多行，则可以设置以下操作之一：

• ON UPDATE NO ACTION ：SQL Server引发错误并回滚父表中行的更新操作。
• ON UPDATE CASCADE ：当父表中的行更新时，SQL Server更新子表中的相应行。
• ON UPDATE SET NULL ：当更新父表中的相应行时，SQL Server将子表中的行设置为NULL。 请注 意，外键列必须可以为空以便执行 此操作。
• ON UPDATE SET DEFAULT ：SQL Server为子表中的行设置默认值，这些行更新了父表中的相应 行。

3.SQL Server Not Null约束

SQL Server NOT NULL约束简介

SQL Server NOT NULL 约束只是指定列不能为 NULL 值。

以下示例为列创建一个具有 NOT NULL 约束的表： persons ，它具有以下几个字段： first_name ， last_name 和 email ：

CREATE SCHEMA hr;
GO
CREATE TABLE hr.persons(
person_id INT IDENTITY PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20)
);

请注意，上面几列中，将 NOT NULL 约束作为列约束。

默认情况下，如果未指定 NOT NULL 约束，则SQL Server将允许该列接受 NULL 。

在此示例中，只有 phone 列可以接受 NULL 。

1.将NOT NULL约束添加到列

要将 NOT NULL 约束添加到现有列，可以分两步执行：

首先，更新表的值，以便列中没有 NULL 值：

UPDATE table_name
SET column_name = <new_value>
WHERE column_name IS NULL;

其次，更改表以更改列的属性：

ALTER TABLE table_name
ALTER COLUMN column_name data_type NOT NULL;

例如，要将 NOT NULL 约束添加到 hr.persons 表的 phone 列，请使用以下语句。

首先，如果某些人员没有电话号码，则将电话号码更新为公司电话号码，例如， 0898-88889999 ：

UPDATE hr.persons
SET phone = "0898-88889999"
WHER phone IS NULL;

第二步，修改 phone 列的属性为 NOT NULL ：

ALTER TABLE hr.persons
ALTER COLUMN phone VARCHAR(20) NOT NULL;

2.删除NOT NULL约束

要从列中删除 NOT NULL 约束，请使用 ALTER TABLE ALTER COLUMN 语句，如下所示：

ALTER TABLE table_name
ALTER COLUMN column_name NULL;

例如，要从 phone 列中删除 NOT NULL 约束，请使用以下语句：

ALTER TABLE hr.pesons
ALTER COLUMN phone NULL;