小白学习MySQL Day4-5 20240818-0819

一、表约束 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）Defined when creating a table

CREATE TABLE name (

col-name datatype [col-options],

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、特点

（1）外键的个数：不唯一

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

（2）外键的值：可为空，可重复

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

外键列中的值是可以重复的，因为外键约束并不要求外键列中的每个值都是唯一的。

外键约束只要求：外键列中的每个非空值都必须在主 / 表被引用表（即包含主键或唯一键的表）的相应列中存在。

•  保持数据一致性：

外键约束 确保 从表中的外键列值 有匹配或为空。只有这两种可能。

要么在主表的主键或唯一键中有匹配，要么为空。

这有助于保持引用完整性，防止孤立的行存在。

•  级联更新和删除：

在定义外键时，可以指定级联更新（ON UPDATE）和级联删除（ON DELETE）的行为。

1）如果指定了级联更新，当主表中的主键被更新时，从表中的相应外键也会随之自动更新。

2）如果指定了级联删除，当主表中的主键被删除时，从表中的相应行也会随之被删除。

3）如果不指定这些行为，数据库默认会阻止更新或删除操作，以避免破坏引用完整性。

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

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

（3）外键中列的数据类型：

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

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)

REFERENCES branch (branchNo)

ON DELETE SET NULL

ON UPDATE CASCADE

);

What will happen to the staff table if the following lines were executed?

1. DELETE FROM branch WHERE branchNo = 'B001’;

2. UPDATE branch SET branchNo = 'B007’ WHERE branchNo = 'B005’;

1.主表branch中符合branchNo = 'B001’的一行被删去，则从表staff中所有'B001'的值会设为空。

2.主表branch中符合branchNo = 'B005’的值被改为'B007'，

   则从表staff中所有'B005'的值会随之被改为'B007'。

Q3: Assume that branch table consists three branchNo B001, B002 and B005.

 Insert following tuples into staff table

• ('S1', 'staff1', 'b001'),

• ('S2', 'staff2', 'B007'),

• ('S3', 'staff3', 'B001'),

• ('S4', 'staff4', 'B002'),

• ('S1', 'staff5', 'B002’);

Which step will cause error?

第二个元组 ('S2', 'staff2', 'B007')：

因为它引用了一个不存在于主表的 branchNo。
第五个元组 ('S1', 'staff5', 'B002’)：

错误1：引号错误，应该使用英文符号。会因为语法错误而无法执行。

错误2：第四个元组有效，则staff表中已经存在B002。再次插入相同值，会因为主键冲突而失败。

大小写问题：

In MySQL, string comparison is case-insensitive. 字符串比较是不区分大小写的。

默认情况下，MySQL不会考虑字符的大小写差异。

也就是说，大写字母和小写字母被视为是相同的，这在进行比较操作时会导致相同的结果。

E.g. 假设第一列branchNo是branch表的主键，

INSERT INTO branch VALUES

('B001', 'city1', 'street1', 'postcode1'),

('b001', 'city1x', 'street1x', 'postcode1x');

 The 'b001' above will violate(违反) the primary key constraint. 主键值应该唯一。

如何解决？使用 BINARY 。

CREATE TABLE branch (

branchNo CHAR(4) BINARY NOT NULL,

PRIMARY KEY (`branchNo`),

...

);

二、BINARY 

首先，BINARY是数据类型，不是约束。

指定了列存储数据的格式（二进制字符串）。

用途：

1、数据类型定义：

BINARY 和 VARBINARY 类型用于存储二进制数据。

这些类型的数据，在比较和排序时是基于字节值的，而不是基于字符集。

2、区分大小写：

当使用BINARY类型的列进行比较时，MySQL会区分字符串中的大小写。

这与CHAR或VARCHAR类型（默认不区分大小写）的行为不同。

The BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters.