SQL的约束（上）

TTYLXWT

已于 2022-09-19 19:31:40 修改

阅读量342

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库

于 2022-09-19 19:24:06 首次发布

本文链接：https://blog.youkuaiyun.com/TTYLXWT/article/details/126939832

SQL的约束（上）：

er图：

A、SQL Server主键约束

PRIMARY KEY简介：

主键是唯一标识表中每一行的列或一组列。可以使用 PRIMARY KEY 约束为表创建主键。

如果主键只包含一列，则可以将 PRIMARY KEY 约束定义为列约束：

如果主键有两列或更多列，则必须使用 PRIMARY KEY 约束作为表约束：

每个表只能有一个主键。参与主键的所有列必须定义为 NOT NULL 。

如果没有为这些列指定 NOT NULL 约束，SQL Server会自动为所有主键列设置 NOT NULL 约束。在创建主键时，SQL Server还会自动创建唯一的聚簇索引(如果指定，则为非聚集索引)。

PRIMARY KEY约束示例

以下示例创建一个包含主键的表，该主键包含一列：

在此 sales.activities 表中， activity_id 列是主键列。 activity_id 列包含唯一值。

IDENTITY 属性用于 activity_id 列以自动生成唯一的整数值。

以下语句创建一个名为 sales.participants 的新表，其主键由两列组成：

在此示例中， activity_id 或 customer_id 列中的值可以重复，但两列中的每个值组合必须是唯一的。

通常，表始终具有在创建时定义的主键。但是，有时，现有表可能由于某种原因而没有定义主键。在这种情况下，可以使用 ALTER TABLE 语句向表中添加主键。请考虑以下示例。

以下语句创建没有主键的表：

要将 event_id 列作为主键，请使用以下 ALTER TABLE 语句：

SQL PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

SQL PRIMARY KEY Constraint on CREATE TABLE

下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：

MySQL:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),

PRIMARY KEY (Id_P)

)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULLPRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)

SQL PRIMARY KEY Constraint on ALTER TABLE

如果在表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

撤销 PRIMARY KEY 约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

B、SQL Server外键约束

SQL Server外键约束简介；

外键是一个表中的一列或一组列，它唯一地标识另一个表的行。 vendor_groups 和v endor 表，它们的结构如下：

每个供应商属于供应商组，每个供应商组可能有零个或多个供应商。 vendor_groups 和 vendors 表之间的关系是一对多的。

对于 vendors 表中的每一行，始终可以在 vendor_groups 表中找到相应的行。

但是，如果使用当前表创建方式，可以在 vendors 表中插入一行而不在 vendor_groups 表中显示相应的行。

还可以删除 vendor_groups 表中的行，而无需更新或删除 vendors 表中导致vendors 表中存在孤立的行。

要强制执行 vendor_groups 和 vendors 表中的数据之间的链接，需要在 vendors 表中建立外键。

要创建外键，请使用 FOREIGN KEY 约束。

以下语句删除 vendors 表并使用 FOREIGN KEY 约束重新创建它：

现在， vendor_groups 表称为父表，该表是外键约束引用的表。 vendors 表称为子表，该表是应用外键约束的表。

在上面的语句中，以下子句创建名为 fk_grou p的 FOREIGN KEY 约束，该约束将 vendors 表中的 group_id 链接到 vendor_groups 表中的 group_id ：

FOREIGN KEY约束语法：

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

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

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

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

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

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

FOREIGN KEY约束示例：

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

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

上面语句按预期工作。

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

SQL Server发出以下错误：

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

参考操作：

外键约束确保了引用完整性。如果父表中存在相应的行，则只能在子表中插入一行。此外，外键约束用于在更新或删除父表中的行时定义引用操作，如下所示：

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为子表中的行设置默认值，这些行更新了父表中的相应行。

SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

让我们通过一个例子来解释外键。请看下面两个表：

"Persons" 表：

“Orders” 表：

请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。

"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

SQL FOREIGN KEY Constraint on CREATE TABLE

下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：

MySQL:

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),

FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)