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)
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 = 'B007’的值被改为'B005',
则从表staff中所有'B007'的值会随之被改为'B005'。
Q3: Assume that branch table consists three branchNo B001, B002 and B005.
Insert following tuples into staff table
•
('S1', 'staff1', '
b
001'),
•
('S2', 'staff2', '
B
007'),
•
('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.