小白学习MySQL Day4-5 20240818-0819

小白stepbystep

已于 2024-08-19 22:50:16 修改

阅读量876

点赞数 17

文章标签：学习 mysql 数据库

于 2024-08-19 22:44:16 首次发布

本文链接：https://blog.csdn.net/stepbystep_0/article/details/141293224

版权

一、表约束 Table Constraints

1、语法

CONSTRAINT constraint_name TYPE (details);

• 为什么要给表约束命名？

当需要修改或删除时，可以通过引用它的名字来进行操作。

Constraint name is created so that later this constraint can be removed by referring to its name.

（1）D efined when creating a table

CREATE TABLE name (

col-name datatype [col-options] ,

...

[constraint-1],

[constraint-n]

);

（2）Added to an existing table

E.g.

ALTER TABLE persons
ADD CONSTRAINT id_positive CHECK (id>0)

2、类型 Constraint Types

(1) CHECK

1) 特点：

Limit the possible values of an attribute. 用于限制表中的属性/列中的可能值。

Supported in MySQL 8.0.16+

CHECK约束只能访问其所在表的列。

在使用CHECK约束时，确保条件表达式不依赖于其他表的数据。

2）语法：

• 创建：

CREATE TABLE name (

column1 Data_Type [constraint],

column2 Data_Type [constraint],

...

CONSTRAINT constraint_name CHECK (condition )

);

ALTER TABLE tablename

ADD CONSTRAINT constraint_name CHECK (condition );

这里的condition是一个布尔表达式，它定义了列值必须满足的条件。

如果尝试插入或更新表中的行，并且这些行的值不满足CHECK约束中的条件，

那么操作将失败并返回一个错误。

• 撤销：

ALTER TABLE tablename
DROP CHECK constraint_name;

• 多个列定义 CHECK 约束：

ALTER TABLE persons
ADD CONSTRAINT chk_person CHECK (Id>0 AND City='Sandnes' );

(2) UNIQUE

1) 特点：

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

UNIQUE 和 PRIMARY KEY 约束，均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

The following unique keys are different:

• One unique key (a, b, c):

The combination of columns a, b, c must be unique.

Tuples (1, 2, 3) and (1, 2, 2) are allowed.

• Separate unique keys (a), (b), and (c):

Each single column does not allow duplicates.

Tuples (1, 2, 3) and (1, 2, 2) are NOT allowed.

2）语法： CONSTRAINT name UNIQUE (col1, col2, …)

• 创建：

CREATE TABLE name (

column1 Data_Type [constraint],

column2 Data_Type [constraint],

...

CONSTRAINT constraint_name UNIQUE (column1 )

);

column1 must be unique.

ALTER TABLE tablename

ADD CONSTRAINT constraint_name UNIQUE (column1, column2 );

The combination of column1 and column2 must be unique.

• 撤销：

ALTER TABLE tablename
DROP INDEX constraint_name;

通过 CONSTRAINT 关键字定义的 UNIQUE 约束，若想要撤销，需要找到这个约束在内部是如何被实现的（通常是通过一个对应的唯一索引），然后使用 DROP INDEX 语句来删除它。

(3) PRIMARY KEY

1) 特点：非空且唯一，唯一标识每条记录

• Constraint name for a primary key is ignored by MySQL but works in other databases.

The primary key and unique key will automatically be assigned with constraint names.

• Values in primary key columns cannot be NULL.

• Only one primary key is allowed for a table.

2) 语法：

• 创建：

• 撤销：

ALTER TABLE tablename
DROP INDEX PRIMARY KEY;

• 复合主键（由多个列组成）：

E.g. Assume that columns (booking_time, guest_id) should be set as the primary key:

CREATE TABLE room_booking (

booking_time DATETIME,

room_number INT,

guest_id VARCHAR(100),

comments TEXT,

PRIMARY KEY (booking_time, guest_id)

);

(4) FOREIGN KEY

1、基本概念

主表（Parent Table）：包含被其他表通过FOREIGN KEY引用的列的表。
从表（Child Table）：含FOREIGN KEY的表，该FOREIGN KEY指向主表中的一个列。
一个表(从表)中的 FOREIGN KEY 指向另一个表(主表)中的 PRIMARY KEY或UNIQUE KEY。

2、特点

一张表可以一个或多个外键，与多张表进行关联。

外键可以为空值；若不为空值，则每一个外键的值必须等于主表中主键的某个值。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

• 保持数据一致性：确保从表中的外键列只包含主表中实际存在的值
• 级联更新和删除：

当主表中的主键值被更新或删除时，可以自动更新或删除从表中的相应行，以保持数据的一致性。

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。

3、构成

A foreign key consists of following parts:

• A constraint name.

• Columns of the referencing table.

• Referenced table.

• Referenced columns.

• Reference options.

1) 约束名称（Constraint Name）

可选的标识符，用于唯一地命名外键约束。虽然它不是必需的，但为外键约束提供一个明确的名称可以使得在将来需要修改或删除该约束时更加容易引用。

2) 引用表的列（Columns of the Referencing Table）/ 从表的列

这些列的值必须匹配被引用表（主表）中的相应列的值。

3) 被引用表 / 主表（Referenced Table)

被引用表是包含外键所引用的主键或唯一键的表。

4) 被引用列（Referenced Columns）

被引用列是外键所指向的具体列，

这些列在父表中必须是唯一的（通常是主键或具有唯一约束的列）。

5) 引用选项（Reference Options）

在MySQL中，当定义一个外键（FOREIGN KEY）约束时，

• ON DELETE – What will happen if referenced values are deleted. 主表中的被引用值被删

• ON UPDATE – What will happen if referenced values are updated. 主表中的被引用值被改

ON UPDATE 和 ON DELETE子句 允许我们指定：

当被引用的主表中的数据发生变化时（即：行被更新或删除时），子表中的外键列应该如何响应。

这些响应选项（ref_opt）包括RESTRICT、CASCADE、SET NULL。

但是，MySQL并不直接支持SET DEFAULT作为外键的ON DELETE或ON UPDATE选项。

若未设置引用选项，则会使用默认值 RESTRICT。

1. RESTRICT

当使用RESTRICT选项时，如果尝试删除或更新主表中的行，

而这些行在子表中被外键引用，则数据库系统会拒绝该删除或更新操作。

RESTRICT – stop the user from doing it.

为了确保数据的一致性，防止因为主表数据的更改而导致子表中出现“悬挂”的引用。

在MySQL中，RESTRICT是默认行为（如果没有明确指定其他选项）。The default option.

但在某些数据库系统中可能需要显式指定。

报错的具体情境：

• 主表删除行，其主键值在从表里被外键引用，则会被阻止删除。

若想删除，必先删除从表的相关行。

• 主表修改主键值，这个值在从表里被外键引用而存在，则会被阻止修改。

若想修改，必先删除从表的相关行。

• 从表插入新行，外键值不在主表中，则会被阻止插入。

• 从表修改外键值，新值不是主表的主键值，则会被阻止修改。

2. CASCADE

CASCADE选项指定，当主表中的行被删除或更新时，

子表中所有引用该行的外键列也将自动被相应地删除或更新。

CASCADE – let the changes flow on.

这有助于保持数据的一致性，但需要谨慎使用，因为它可能会导致级联删除或更新大量的数据行。

3. SET NULL

当使用SET NULL选项时，如果父表中的行被删除，

则子表中所有引用该行的外键列的值将被设置为NULL。

SET NULL – make referencing values null.

要求外键列必须能够接受NULL值。

这种方式允许子表中的数据保留下来，但移除了与父表之间的直接联系。

注：SET DEFAULT 在MySQL中不支持

当使用SET DEFAULT选项时，如果父表中的行被删除，

则子表中所有引用该行的外键列的值将被设置为默认值。

要求外键列有明确的默认值定义，并且该默认值在逻辑上是合理的。

• ON DELETE – What will happen if referenced values are deleted.

• ON UPDATE – What will happen if referenced values are updated

Q1: Which of the following tuples can be inserted into staff given the current values of branch?

需要检查每个元组（tuple）中的 branchNo 是否在 branch 表中已经存在。

即：若从表插入新行，则新行的外键值必须在主表中。

Adding non-existing branchNo to staff table will be rejected by DBMS.

(S1, staff1, B001): 如果 B001 在 branch 表中存在，则可以插入。
(S2, staff2, B007): 如果 B007 在 branch 表中存在，则可以插入。
(S4, staff4, B002): 如果 B002 在 branch 表中存在，则可以插入。
(S5, staff5, NULL): branchNo 为 NULL，这通常是可以接受的

因为外键允许 NULL 值（除非有额外的 NOT NULL 约束）。

Q2:

CREATE TABLE branch (

branchNo CHAR(4) BINARY NOT NULL ,

PRIMARY KEY (branchNo)

);

CREATE TABLE staff (

staffNo CHAR(6) PRIMARY KEY,

Name VARCHAR(20),

branchNo CHAR(4) ,

CONSTRAINT FK_staff_branchNo

FOREIGN KEY (branchNo)