SQL - Table and Data Part 2

一、Table Constraints

Table constraints can be defined when creating tables.

But you can also add constraints to an existing table.

1. Syntax of Constraints

• General Syntax:

CONSTRAINT name TYPE details;

• 约束名称是为了以后可以通过该名称删除(removed)约束而创建的。

• 如果没有提供名称，将自动生成(generated)一个名称。

MySQL provides the following constraint types:

• PRIMARY KEY

• UNIQUE

• FOREIGN KEY

• CHECK

• INDEX

2. CHECK Constraint

• 通过添加 CHECK 约束可以限制属性的可能值。(limit the possible values of an attribute) 

Example:

 

CREATE TABLE People(id INTERGER PRIMARY KEY,name VARCHAR(100) NOT NULL,CONSTRAINT id_positive CHECK (id > 0)
); 

3. UNIQUE Constraint

• 一个表可以定义多个(multiple) UNIQUE 键。

CONSSTRAINT name UNIQUE (col1,col2,……）

• 组合唯一键（如 (a, b, c)）：

Composite unique key 

• 列 a、b 和 c 的组合必须唯一，但单独的列值（individual column)可以重复。

• 单独唯一键（如 (a)、(b) 和 (c)）：

Separate unique keys 

• 每个单独的列不允许重复。( duplicates)

4. Primary Key

• 主键约束名称在 MySQL 中被忽略，但在其他数据库中有效。

• 主键列的值不能为空。

• 一个表只能有一个主键。

Only one primary key is allowed for a table.

CONSTRAINT name PRIMARY KEY(col1,col2……）

示例：

Example:

CREATE TABLE Branch (branchNo CHAR(4),street VARCHAR(100),city VARCHAR(25),postcode VARCHAR(7),CONSTRAINT branchUnique UNIQUE(postcode),CONSTRAINT branchPKPRIMARY KEY (branchNo)
);

alternative way

CREATE TABLE Branch2 (branchNo CHAR(4) PRIMARY KEY,street VARCHAR(100),city VARCHAR(25),postcode VARCHAR(7) UNIQUE
);

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

Question

（1）. (Col1)：合法，因为 Col1 中的值都是唯一的，没有重复。

（2）. (Col4)：不合法，因为 Col4 中的值有重复（4 出现了三次）。

（3）. (Col1, Col4)：合法，组合后的值是唯一的。

（4）. (Col2, Col1)：合法，组合后的值是唯一的。

（5）. (Col2, Col3)：不合法，因为 (NULL, 2) 和 (NULL, 5) 组合后有重复，NULL 无法唯一标识。

（6）. (Col3, Col4)：合法，组合后的值是唯一的。

CREATE TABLE room_booking (booking_time DATETIME,room_number INT,guest_id VARCHAR(100),comments TEXT,PRIMARY KEY (booking_time, guest_id)
);

CREATE TABLE room_booking (booking_time DATETIME,room_number INT,guest_id VARCHAR(100),comments TEXT,CONSTRAINT pk_room_booking PRIMARY KEY (booking_time, guest_id)
);

二、Foreign Key

 外键包括以下部分：（consists of the following parts）

• 约束名称。A constraint name.

• 引用表的列。Columns of the referencing table.

• 被引用的表和列。Referenced table and referenced columns.

• 引用选项（如 ON DELETE 和 ON UPDATE）。

Reference options 

1. Syntax:

2. Foreign Key Important Notice

（1） 外键必须引用唯一键（unique key)或主键。

（2） 列表必须用括号括起来。

Column list must be enclosed with brackets.

（3）两列的数据类型必须兼容。

Data types of both columns must be compatible.

​​​​​​​

• 应用外键约束后，`staff`. `branchNo`(the branchNo column of table staff) 列的值将由数据库检查，确保它们是：

• `branch`.`branchNo` 表中的现有值(existing value)， 或者 NULL。

CREATE TABLE P(Col4 INT,Col5 INT,Col6 INT,CONSTRINT PK_4_5 PRIMARY KEY (Col4,Col5)
);CREATE TABLE T(Col1 INT,Col2 INT,Col3 INT,CONSTRINT FK_T_2_3 FOREIGN KEY(Col1,Col2)REFERENCESP(Col4,Col5)
);

• 可插入的元组为那些 branchNo 存在于 branch 表中，或 branchNo 值为 NULL 的元组。

The tuples that can be inserted are those where branchNo exists in the branch table or branchNo is NULL.

Q2:

• ('S5', 'staff5', NULL)：NULL 值允许存在于外键中，因为它表示无引用。

不能插入的元组：

• ('S2', 'staff2', 'B007')：B007 不在 branch 表中，因此会违反外键约束。

Q3:

• 在 Branch 表中，branchNo 是主键，因此它必须唯一。只有 B007 不存在于 Branch 表中，因此可以插入。

在这个实验问题中，我们需要将一些元组插入到 staff 表中，并检查插入操作是否会导致错误。右侧的 branch 表提供了 branchNo 列的有效值，staff 表中的 branchNo 列有一个外键约束，要求它引用 branch 表中的 branchNo 列。这意味着 staff 表中插入的 branchNo 值必须在 branch 表中存在，或者为 NULL，否则会导致外键约束错误。

• 插入 b001 会触发外键约束错误，因为 MySQL 默认不区分大小写，但外键引用需要严格匹配 branch 表中的值。

• 插入 B007 会触发外键约束错误，因为 B007 不存在于 branch 表中。

3. MySQL: String Comparison

• 在 MySQL 中，字符串比较不区分大小写。(case-insensitive),

• 例如，插入 B001 和 b001 会违反主键约束。

 violate the primary key constraint.

解决方案：使用 BINARY 关键字，使比较区分大小写。

4. The BINARY Keyword

• BINARY 关键字指示 MySQL 按字符串的 ASCII 值进行比较，而不仅仅是字母。

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

• In other databases, string comparison can be implemented differently.

CREATE TABLE `branch` (`branchNo` char(4) BINARY NOT NULL,PRIMARY KEY(`branchNo`),
);

三、Reference Options

add non-existing branchNo to Staff were rejected by DBMS.

What happens when we change/delete existing branchNo in Branch that are being referenced bt Staff. 

1. RESTRICT：

阻止用户删除或更新外键约束的值。under foreign key constraints.

2. CASCADE：

允许更改传播到引用的表。Allow the changes to flow on to the referenced table.

在删除或更新父记录时自动删除或更新子表中相关记录的情况

3. SET NULL：

将引用的值设置为 NULL。Set referencing values to NULL.

当删除或更新父表中的记录时，可以选择将子表中对应的外键值设置为 NULL，前提是外键列允许 NULL 值。

4. SET DEFAULT：

将引用的值设置为列的默认值。 Set referencing values to the default value for their column.

当删除或更新父表中的记录时，可以选择将子表中对应的外键值设置为预定义的默认值。

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

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

CREATE TABLE branch (branchNo CHAR(4)PRIMARY KEY
);CREATE TABLE staff(staffID CHAR(2),staffName VARCHAR(50),branchNo CHAR(4),FORIGN KEY (branchNo) REFERENCE branch(branchNo)ON DELETE SET NULLON UPDATE CASCADE
);

注意

• 只有整个 CREATE TABLE 语句的最后需要一个分号 ;

• 在外键约束内部的各行不需要添加分号，否则会导致语法错误。

EX：

四、Altering Tables

• 添加列：

1. Add Column:

ALTER TABLE table_nameADD column_name datatype [options like UNIQUE];

• 删除列：

2. Drop Column:

ALTER TABLE table_baneDROP COLUMN column_name;

• 修改列名和定义：

3. Modify Column Name and Definition:

ALTER TABLE table_nameCHANGE COLUMNcol_name new_col_name datatype [col_options];

ALTER 语句通常用于在表已经创建好之后进行修改，因此它通常不会在 CREATE 语句中使用。

整个语句最后需要一个分号 ; 来表示 SQL 语句的结束。

EX：

ALTER TABLE staff ADD `IName` VARCHAR(20) NOT NULL;ALTER TABLE staff DROP COLUMN `IName`;ALTER TABLE staff CHANGE COLUMN `fName``first_name` VARCHAR(20) NOT NULL;ALTER TABLE staff MODIFY COLUMN`first_name` VARCHAR(40)_ NOT NULL;

4. Adding Constraints

ALTER TABLE table_nameADD CONSTRAINT name definition;

EX:

ALTER TABLE branchADD CONSTRAINT ck_branch UNIQUE (street);ALTER TABLE staff ADD CONSTRAINT fk_staff_staffFOREIGN KEY (branchNo) REFERENCES branch (branchNo);

5. Removing Constraints

DROP INDEX：当添加一个唯一键时，数据库会自动创建一个唯一索引来支持这个唯一性约束。因此，删除唯一键约束时，通常也可以通过删除唯一索引来实现。

DROP CHECK：删除表中的检查约束（CHECK constraint）

EX：

ALTER TABLE staff DROP PRIMARY KEY;ALTER TABLE staff DROP FORIGN KEY fk_staff_staff;ALTER TABLE branch DROP INDEX ck-branch;

CREATE TABLE employees (employee_id INT PRIMARY KEY,salary DECIMAL(10, 2),CONSTRAINT chk_salary CHECK (salary > 0)
);ALTER TABLE employees DROP CHECK chk_salary;ALTER TABLE employees DROP PRIMARY KEY;

五、Deleting Tables

• 可以使用 DROP 关键字删除表。

• 删除的表及其中的所有数据将被永久删除，且无法撤销。

Dropped tables and all data within them will be permanently deleted and cannot be undone.

• Tables will be dropped in that exact order

• All tuples in the dropped tables will be deleted as well.

Example:

DROP TABLE [IF EXITS] table-name1,table-name2……；

• Foreign Key constraints will prevent DROPS under the default RESTRICT option, to overcome this:

（1） Remove the foreign key constraints first then drop the tables.

先移除外键约束，然后再删除表。

（2）Drop the tables in the correct order (referencing table first).

按正确的顺序删除表（先删除引用的表）。

（3） Turn off foreign key check temporarily.

临时关闭外键检查。

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;-- 进行需要的操作，比如删除表或插入数据-- 重新开启外键检查
SET FOREIGN_KEY_CHECKS = 1;