MySQL-13.MySQL約束_ZenDei技術網路在線

1.約束(constraint)概述 1.1 為什麼需要約束數據完整性（Data Integrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。它是防止資料庫中存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成的無效操作或錯誤信息而提出的。為了保證數據的完整性， ...

1.約束(constraint)概述

1.1 為什麼需要約束

數據完整性（Data Integrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。它是防止資料庫中存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成的無效操作或錯誤信息而提出的。

為了保證數據的完整性，SQL規範以約束的方式對表數據進行額外的條件限制。從以下四個方面考慮：

實體完整性(Entity Integrity)：例如，同一個表中，不能存在兩條完全相同無法區分的記錄。
域完整性(Domain Integrity)：例如，年齡範圍0-120，性別範圍"男/女"。
引用完整性(Referential Integrity)：例如，員工所在部門，在部門表中要存在該部門。
用戶自定義完整性(User-defined Integrity)：例如，用戶名唯一，密碼不能為空等，本部門經理的工資不得高於本部門職工的平均工資的5倍。

1.2 什麼是約束

約束是表級的強制規定。

可以在創建表時規定約束(通過CREATE TABLE語句)，或者在表創建之後通過ALTER TABLE語句規定約束。實際開發中，在對於表的約束，一般都是在表創建時，就定義好的，因為後期對已有數據的表的約束修改，可能是比較麻煩的。

1.3 約束的分類

根據約束數據列的限制，約束可分為：
- 單列約束：約束只修飾一列。
- 多列約束：約束修飾多列數據。
根據約束的作用範圍，約束可分為：
- 列級約束：只能作用在一個列上，在列的定義後面定義。
- 表級約束：可以作用在多個列上，不與列一起，而是單獨定義。
根據約束的作用，約束可分為：
- NOT NULL 非空約束，規定某個欄位不能為空。
- UNIQUE 唯一約束，規定所修飾的欄位在整個表中是唯一的。
- PRIMARY KEY 主鍵(非空且唯一)約束。
- FOREIGN KEY 外鍵約束。
- CHECK 檢查約束。
- DEFAULT 預設值約束。

註意，在MySQL 5.7版本的不支持CHECK約束，定義不會出錯，但也沒有效果。在MySQL 8.0後，支持CHECK約束了。

-- 查看某個表已有的約束
-- information_schema(系統庫名)
-- table_constraints(表名稱，存儲各個表的約束)
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名稱';
-- 註意，該表中，不會存儲表的非空約束和預設值約束

2.非空約束

2.1 作用

限制某個欄位的值不允許為空(非空約束，屬於列級約束，只能修飾一列)。預設和非空約束，都屬於列級約束，只能修飾一列。

2.2 關鍵字

NOT NULL

2.3 特點

預設，所有的類型的值都可以是NULL，包括INT，FLOAT等數據類型。
非空約束只能出現在表對象的列上，只能某個列單獨限定非空，不能組合非空。
一個表可以有很多列都定義非空約束。
空字元串''不等於null，0也不等於null。

2.4 添加非空約束

2.4.1 表創建時

CREATE TABLE table_name(
	column_name1 type1,
    column_name2 type2 NOT NULL,
    column_name3 type3 NOT NULL
)

舉例

CREATE TABLE emp(
	id INT(10) NOT NULL,
    `NAME` VARCHAR(20) NOT NULL,
    gender CHAR NULL
);

CREATE TABLE student(
	sid INT,
	sname VARCHAR(20) not null,
	tel CHAR(11) ,
	cardid CHAR(18) not null
);

insert into student values(1,'張三','13710011002','110222198912032545'); #成功

-- ERROR 1048 (23000): Column 'cardid' cannot be null
insert into student values(2,'李四','13710011002',null);#身份證號為空


insert into student values(2,'李四',null,'110222198912032546');#成功，tel允許為空

-- ERROR 1048 (23000): Column 'sname' cannot be null
insert into student values(3,null,null,'110222198912032547');#失敗

2.4.2 創建表後

-- 其實本質就是對於列的修改，對於未加非空約束的列，添加時非空約束
ALTER TABLE table_name modify 欄位名 數據類型 NOT NULL;

舉例

ALTER TABLE emp modify gender CHAR NOT NULL;

ALTER TABLE student modify sname VARCHAR(20) NOT NULL;

2.5 刪除非空約束

#去掉not null，相當於修改某個非註解欄位，該欄位允許為空
alter table 表名稱 modify 欄位名 數據類型 NULL;

# 或

#去掉not null，相當於修改某個非註解欄位，該欄位允許為空
alter table 表名稱 modify 欄位名 數據類型;-- 會自動添加null

舉例

ALTER TABLE emp
MODIFY gender VARCHAR(30) NULL;

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

註意，有的約束，在定義時，mysql系統會自動創建與之對應的索引，但是非空約束比較簡單，沒有與之對應的索引，所以不要刪除，後續會有。

3.唯一性約束

3.1 作用

用來限制某個欄位/某列的值不能重覆。註意，如果某列，使用了UNIQUE約束，但是沒有NOT NULL約束，則該列，可以存在多個null值。

3.2 關鍵字

UNIQUE

3.3 特點

同一個表可以有多個唯一約束。
唯一約束可以是某一列的值唯一，也可以多個列組合的值唯一。
唯一性約束允許列值為空。
在創建唯一性約束時，如果不給唯一約束命名，預設和列名相同。
MySQL會給唯一約束的列上預設創建一個唯一索引。

3.4 添加唯一約束

3.4.1 建表時

-- 列級約束方式創建唯一性約束，這種方式不能給約束起名，只能由mysql使用列名預設起名
CREATE TABLE table_name(
	column_name1 type1,
    column_name2 type2 UNIQUE,
    column_name3 type3 UNIQUE KEY,
    column_name4 type4 
);

-- 表記約束方式創建唯一性約束，這種方式可以為約束起名
CREATE TABLE table_name(
	column_name1 type1,
    column_name2 type2,
    column_name3 type3,
    [constraint 約束名] UNIQUE KEY(列名)
);
-- []包括的可以省略，省略則使用預設的列名為此唯一性約束命名

舉例

create table test_unique(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);

INSERT INTO test_unique VALUES(1,'張三','13710011002','101223199012015623');-- 成功
INSERT INTO test_unique VALUES(2,'李四','13710011003','101223199012015624');-- 成功

mysql> SELECT * FROM test_unique;
+------+-------+-------------+--------------------+
| sid  | sname | tel         | cardid             |
+------+-------+-------------+--------------------+
|    1 | 張三  | 13710011002 | 101223199012015623 |
|    2 | 李四  | 13710011003 | 101223199012015624 |
+------+-------+-------------+--------------------+
2 rows in set (0.00 sec)

-- ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'test_unique.cardid'
INSERT INTO test_unique VALUES(3,'王五','13710011004','101223199012015624'); #身份證號重覆

-- ERROR 1062 (23000): Duplicate entry '13710011003' for key 'test_unique.tel' 手機號重覆
INSERT INTO test_unique VALUES(3,'王五','13710011003','101223199012015625');

3.4.2 建表後指定唯一約束

#欄位列表中如果是一個欄位，表示該列的值唯一。如果是兩個或更多個欄位，那麼複合唯一，即多個欄位的組合是唯一的
#方式1：
ALTER TABLE 表名稱 ADD [constraint 約束名] UNIQUE KEY(欄位列表);

#方式2：
ALTER TABLE 表名稱 MODIFY 欄位名 欄位類型 UNIQUE;

舉例

ALTER TABLE test_unique
ADD UNIQUE(id);

ALTER TABLE test_unique
MODIFY id int(20) UNIQUE;

3.5 添加複合唯一約束

create table 表名稱(
欄位名 數據類型,
欄位名 數據類型,
欄位名 數據類型,
unique key(欄位列表) 
#欄位列表中寫的是多個欄位名，多個欄位名用逗號分隔，表示那麼是複合唯一，即多個欄位的組合是唯一的
);

舉例

CREATE TABLE USER(
	id INT NOT NULL,
	NAME VARCHAR(25),
	PASSWORD VARCHAR(16),
	-- 使用表級約束語法
	CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
-- name和password列組合的值，不能重覆

INSERT INTO `user`(id,`name`,`password`)
VALUES(1,'wind','123');-- 成功

INSERT INTO `user`(id,`name`,`password`)
VALUES(1,'wind','1233');-- 成功

-- 錯誤 Duplicate entry 'wind-123' for key 'user.uk_name_pwd'
INSERT INTO `user`(id,`name`,`password`)
VALUES(2,'wind','123');

3.6 刪除唯一約束

添加唯一性約束的列上也會自動創建唯一索引。
刪除唯一約束只能通過刪除唯一索引的方式刪除。
刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
如果創建唯一約束時未指定名稱，如果是單列，就預設和列名相同，如果是組合列，預設和第一個的列名相同，也可以自定義唯一性約束名。

ALTER TABLE `USER`
DROP INDEX uk_name_pwd;

-- 可以通過該語句查看表索引
SHOW INDEX FROM 表名;

4.PRIMARY KEY(主鍵)約束

4.1 作用

用來唯一標識表中的一行記錄。

4.2 關鍵字

primary key

4.3 特點

主鍵約束相當於唯一約束 + 非空約束的組合，主鍵約束列不允許重覆，也不允許出現null值。
一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別定義，也可以在表級別上定義。
主鍵約束修飾著表中的一列或者多列(複合主鍵)。
如果是多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重覆。但是實際中，都是使用單列主鍵，很少使用複合主鍵。
MySQL的主鍵名總是PRIMARY，自己命名了主鍵約束也沒用。
當創建主鍵約束時，系統預設會在所在的列或列組合上，建立對應的主鍵索引(能夠根據主鍵查詢的，就根據主鍵查詢，效率更高)。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。但是實際中，不可能出現刪除主鍵約束的情況。
需要特別註意的是，不要修改主鍵欄位的值，因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性。存儲表的結構，是依照主鍵去構建的，值得修改或刪除，可能會導致空洞。

4.4 添加主鍵約束

4.4.1 創建表時

CREATE TABLE test(
	id CHAR(32) primary key, -- 列級模式
    ...
);

CREATE TABLE test(
	id CHAR(32),
    ...,
    [CONSTRAINT name] PRIMARY KEY(id) -- 表級模式
);

舉例

CREATE TABLE test_pri_key(
	id int primary key,
    `name` varchar(20) NOT NULL
);

mysql> DESC test_pri_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

insert into test_pri_key values(1,'張三');#成功
insert into test_pri_key values(2,'李四');#成功

mysql> SELECT * FROM test_pri_key;
+----+------+
| id | name |
+----+------+
|  1 | 張三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

INSERT INTO test_pri_key VALUES(1,'張三');#失敗
-- ERROR 1062 (23000): Duplicate entry '1' for key 'test_pri_key.PRIMARY'

INSERT INTO test_pri_key VALUES(1,'王五');#失敗
-- ERROR 1062 (23000): Duplicate entry '1' for key 'test_pri_key.PRIMARY'

INSERT INTO test_pri_key VALUES(3,'張三');#成功

mysql> SELECT * FROM test_pri_key;
+----+------+
| id | name |
+----+------+
|  1 | 張三 |
|  2 | 李四 |
|  3 | 張三 |
+----+------+
3 rows in set (0.00 sec)

CREATE TABLE test_two_pri_key(
    id INT PRIMARY KEY,
    `name` VARCHAR(20) PRIMARY KEY
);
-- ERROR 1068 (42000): Multiple primary key defined

4.4.2 建表後添加主鍵約束

ALTER TABLE table_name ADD PRIMARY KEY(欄位列表);
-- 欄位列表可以是一個欄位，也可以是多個欄位，如果是多個欄位的話，是複合主鍵

ALTER TABLE test1 ADD PRIMARY KEY(id); -- 只是舉例test1不存在

ALTER TABLE test2 ADD PRIMARY KEY(name,pwd);-- 複合主鍵這種情況，很少使用

4.5 關於複合主鍵

如果是多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重覆。

CREATE TABLE table_name(
	c1 type1,
    c2 type2,
    c3 type3,
    primary key(c1,c2) -- 表示c1和c2的組合是唯一的，也可以有更多欄位
);

-- cpe(composite)
CREATE TABLE test_cpe_pri(
	id int,
    name varchar(20),
    pwd varchar(20),
    constraint t1 PRIMARY KEY(name,pwd) -- 自定義，主鍵約束名為t1
);

-- 該語句，查詢出，test_cpe_pri的主鍵約束名為primary，可見主鍵自定義約束名，也不會生效
SELECT * FROM information_schema.`TABLE_CONSTRAINTS`
WHERE TABLE_NAME = 'test_cpe_pri';

INSERT INTO test_cpe_pri(id,`name`,`pwd`)
VALUES(1,'wind','123');

INSERT INTO test_cpe_pri(id,`name`,`pwd`)
VALUES(1,'wind','1234');

-- 錯誤 1048 Column 'name' cannot be null
INSERT INTO test_cpe_pri(id,`name`,`pwd`)
VALUES(1,NULL,'1234');

-- 錯誤 1062 Duplicate entry 'wind-123' for key 'test_cpe_pri.PRIMARY'
INSERT INTO test_cpe_pri(id,`name`,`pwd`)
VALUES(1,'wind','123');

4.6 刪除主鍵約束

-- 語法格式
ALTER TABLE 表名稱 DROP PRIMARY KEY;

-- 舉例
ALTER TABLE t2 DROP PRIMARY KEY;

ALTER TABLE t3 DROP PRIMARY KEY;

說明，刪除主鍵約束，不需要指定主鍵名，因為一張表只有一個主鍵，刪除主鍵約束後，非空約束還存在。但是不用記這種情況，因為在實際開發中，不可能出現刪除主鍵約束的。

5.自增列：AUTO_INCREMENT

5.1 作用

某個欄位的值自增

5.2 關鍵字

auto_increment

5.3 特點和要求

一個表最多只能有一個自增列。
當需要產生唯一標識或順序值時，可設置自增長。
自增長列約束修飾的列必須是鍵列(主鍵列，唯一鍵列)。
自增約束的列的數據類型必須是整數類型。
如果自增列指定了0和null，會在當前最大值的基礎上自增，如果自增列手動指定了具體值，直接賦值為具體值。

錯誤演示

-- 錯誤代碼： 1075
-- Incorrect table definition; there can be only one auto column and it must be defined as a key
CREATE TABLE test_auto(
	eid INT AUTO_INCREMENT,
    ename VARCHAR(20)
);

-- 錯誤代碼： 1063
-- Incorrect column specifier for column 'ename' ename列不是整數類型
CREATE TABLE test_auto(
	eid INT PRIMARY KEY,
	ename VARCHAR(20) UNIQUE KEY AUTO_INCREMENT
);

5.4 如何指定自增約束

5.4.1 建表時

create table 表名稱(
	欄位名 數據類型 primary key auto_increment, -- 列的數據類型是整數類型
	欄位名 數據類型 unique key not null,
	欄位名 數據類型 unique key,
	欄位名 數據類型 not null default 預設值,
);
create table 表名稱(
	欄位名 數據類型 default 預設值 ,
	欄位名 數據類型 unique key auto_increment, -- 列的數據類型是整數類型
	欄位名 數據類型 not null default 預設值,,
	primary key(欄位名)
);

CREATE TABLE test_auto(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20)
);

mysql> DESC test_auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| eid   | int         | NO   | PRI | NULL    | auto_increment |
| ename | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

5.4.2 建表後

-- 語法格式
ALTER TABLE 表名稱 modify 欄位名 數據類型 auto_increment;

-- 舉例
ALTER TABLE t4 modify id int auto_increment;

5.5 如何刪除自增約束

#alter table 表名稱 modify 欄位名 數據類型 auto_increment;#給這個欄位增加自增約束

alter table 表名稱 modify 欄位名 數據類型; #去掉auto_increment相當於刪除

ALTER TABLE t4 modify id int;

5.6 MySQL 8.0新特性-自增變數的持久化

在MySQL 8.0之前，自增主鍵AUTO_INCREMENT的值如果大於max(primary key)+1，在MySQL重啟後，會重置AUTO_INCREMENT=max(primary key)+1，這種現象在某些情況下會導致業務主鍵衝突或者其他難以發現的問題。下麵通過案例來對比不同的版本中自增變數是否持久化。

在MySQL 5.7版本中，測試步驟如下：創建的數據表中包含自增主鍵的id欄位，語句如下：

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
-- 插入4個空值，執行如下：
INSERT INTO test1
VALUES(0),(0),(0),(0);

查詢數據表test1中的數據，結果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

-- 刪除id為4的記錄，語句如下：
DELETE FROM test1 WHERE id = 4;

-- 再次插入一個空值，語句如下：
INSERT INTO test1 VALUES(0);

查詢此時數據表test1中的數據，結果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)

-- 從結果可以看出，雖然刪除了id為4的記錄，但是再次插入空值時，並沒有重用被刪除的4，而是分配了5。 刪除id為5的記錄，結果如下：
DELETE FROM test1 where id=5;

-- 重啟資料庫，重新插入一個空值。
-- 註意在windows下重啟資料庫服務的命令 先停止 net stop mysql 再開啟net start mysql
-- 如果按照mysql時，自定義了服務名，使用自定義的，需要管理員許可權 
-- 例如我再安裝mysql8.0時，自定義了服務名為MYSQL80所以重啟mysql8.0版本的服務指令為 net stop mysql80, net start mysql80
-- 或者在任務管理器下找到對應服務關閉，啟動也可以
INSERT INTO test1 values(0);

再次查詢數據表test1中的數據，結果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

從結果可以看出，新插入的0值分配的是4，按照重啟前的操作邏輯，此處應該分配6。出現上述結果的主要原因是自增主鍵沒有持久化。在MySQL 5.7系統中，對於自增主鍵的分配規則，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在記憶體中維護，並不會持久化到磁碟中。當資料庫重啟時，該計數器會被初始化。

在MySQL 8.0版本中，上述測試步驟最後一步的結果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
+----+
4 rows in set (0.00 sec)

從結果可以看出，自增變數已經持久化了。

MySQL 8.0將自增主鍵的計數器持久化到 重做日誌中。每次計數器發生改變，都會將其寫入重做日誌中。如果資料庫重啟，InnoDB會根據重做日誌中的信息來初始化計數器的記憶體值。InnoDB是，存儲引擎的一種。後續課程會講解。

6.FOREIGN KEY(外鍵)約束

6.1 作用

限定某個表的某個欄位的引用完整性。

比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。

6.2 關鍵字

FOREIGN KEY

6.3 主表和從表/父表和子表

主表（父表）：被引用的表，被參考的表

從表（子表）：引用別人的表，參考別人的表

例如：員工表的員工所在部門這個欄位的值要參考部門表：部門表是主表，員工表是從表。

例如：學生表、課程表、選課表：選課表的學生和課程要分別參考學生表和課程表，學生表和課程表是主表，選課表是從表。

6.4 特點

（1）從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列

為什麼？因為被依賴/被參考的值必須是唯一的

（2）在創建外鍵約束時，如果不給外鍵約束命名，預設名不是列名，而是自動產生一個外鍵名（例如student_ibfk_1;），也可以指定外鍵約束名。

（3）創建(CREATE)表時就指定外鍵約束的話，先創建主表，再創建從表

（4）刪表時，先刪從表（或先刪除外鍵約束），再刪除主表(類似，正在使用中的文件不能刪除一樣的邏輯，先把文件關閉，就能刪除了)。

（5）當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然後才可以刪除主表的數據

（6）在“從表”中指定外鍵約束，並且一個表可以建立多個外鍵約束

（7）從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣，邏輯意義一致。如果類型不一樣，創建子表時，就會出現錯誤“ERROR 1005 (HY000): Can't createtable'database.tablename'(errno: 150)”。

例如：都是表示部門編號，都是int類型。

（8）當創建外鍵約束時，系統預設會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據外鍵查詢效率很高）

（9）刪除外鍵約束後，必須手動刪除對應的索引

6.5 添加外鍵約束

6.5.1 建表時

create table 主表名稱(
	欄位1 數據類型 primary key,
	欄位2 數據類型
);

create table 從表名稱(
	欄位1 數據類型 primary key,
	欄位2 數據類型,
[CONSTRAINT <外鍵約束名稱>] FOREIGN KEY（從表的某個欄位) references 主表名(被參考欄位)
);

#(從表的某個欄位)的數據類型必須與主表名(被參考欄位)的數據類型一致，邏輯意義也一樣
#(從表的某個欄位)的欄位名可以與主表名(被參考欄位)的欄位名一樣，也可以不一樣

-- FOREIGN KEY: 在表級指定子表中的列
-- REFERENCES: 標示在父表中的列

create table dept( #主表
	did int primary key, #部門編號
	dname varchar(50) #部門名稱
);
create table emp(#從表
	eid int primary key, #員工編號
	ename varchar(5), #員工姓名
	deptid int, #員工所在的部門
	foreign key (deptid) references dept(did) #在從表中指定外鍵約束
	#emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
);
-- 說明：
-- （1）主表dept必須先創建成功，然後才能創建emp表，指定外鍵成功。
-- （2）刪除表時，先刪除從表emp，再刪除主表dept

6.5.2 建表後

一般情況下，表與表的關聯都是提前設計好了的，因此，會在創建表的時候就把外鍵約束定義好。不過，如果需要修改表的設計（比如添加新的欄位，增加新的關聯關係），但沒有預先定義外鍵約束，那麼，就要用修改表的方式來補充定義。

格式

ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的欄位) REFERENCES 主表名(被引用
欄位) [on update xx][on delete xx];

舉例

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id)

6.6 演示問題

-- （1）失敗：不是鍵列
-- ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是鍵列
create table dept(
	did int , #部門編號
	dname varchar(50) #部門名稱
);
create table emp(
	eid int primary key, #員工編號
	ename varchar(5), #員工姓名
	deptid int, #員工所在的部門
	foreign key (deptid) references dept(did)
);

-- （2）失敗：數據類型不一致
-- ERROR 1215 (HY000): Cannot add foreign key constraint 原因是從表的deptid欄位和主表的did字
-- 段的數據類型不一致，並且要它倆的邏輯意義一致
create table dept(
	did int primary key, #部門編號
	dname varchar(50) #部門名稱
);
create table emp(
	eid int primary key, #員工編號
	ename varchar(5), #員工姓名
	deptid char, #員工所在的部門
	foreign key (deptid) references dept(did)
);

-- （3）成功，兩個表欄位名一樣
create table dept(
	did int primary key, #部門編號
	dname varchar(50) #部門名稱
);
    create table emp(
    eid int primary key, #員工編號
    ename varchar(5), #員工姓名
    did int, #員工所在的部門
    foreign key (did) references dept(did)
    #emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
    #是否重名沒問題，因為兩個did在不同的表中
);

-- （4）添加、刪除、修改問題
create table dept(
    did int primary key, #部門編號
    dname varchar(50) #部門名稱
);
create table emp(
    eid int primary key, #員工編號
    ename varchar(5), #員工姓名
    deptid int, #員工所在的部門
    foreign key (deptid) references dept(did)
    #emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
);

insert into dept values(1001,'教學部');
insert into dept values(1003, '財務部');
insert into emp values(1,'張三',1001); #添加從表記錄成功，在添加這條記錄時，要求部門表有1001部門
insert into emp values(2,'李四',1005);#添加從表記錄失敗
-- ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row: a foreign key
-- constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`)
-- REFERENCES `dept` (`did`)) 從表emp添加記錄失敗，因為主表dept沒有1005部門

mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教學部 |
| 1003 | 財務部 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 張三 | 1001 |
+-----+-------+--------+
1 row in set (0.00 sec)

update emp set deptid = 1002 where eid = 1;#修改從表失敗
-- ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row（子表的記錄）: a
-- foreign key constraint fails（外鍵約束失敗） (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1`
-- FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))
#部門表did欄位現在沒有1002的值，所以員工表中不能修改員工所在部門deptid為1002

update dept set did = 1002 where did = 1001;#修改主表失敗
-- ERROR 1451 (23000): Cannot delete（刪除） or update（修改） a parent row（父表的記錄）: a
-- foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY
-- (`deptid`) REFERENCES `dept` (`did`)) 
#部門表did的1001欄位已經被emp引用了，所以部門表的1001欄位就不能修改了。

update dept set did = 1002 where did = 1003;
#修改主表成功 因為部門表的1003部門沒有被emp表引用，所以可以修改

delete from dept where did=1001; #刪除主表失敗
-- ERROR 1451 (23000): Cannot delete（刪除） or update（修改） a parent row（父表記錄）: a
-- foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY
-- (`deptid`) REFERENCES `dept` (`did`)) 
#因為部門表did的1001欄位已經被emp引用了，所以部門表的1001欄位對應的記錄就不能被刪除

總結：外鍵約束關係是針對雙方的

添加了外鍵約束後，主表的修改和刪除數據受約束
添加了外鍵約束後，從表的添加和修改數據受約束
在從表上建立外鍵，要求主表必須存在
刪除主表時，要求從表從表先刪除，或將從表中外鍵引用該主表的關係先刪除

6.7 約束等級

Cascade方式：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
Set null方式：在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但是要註意子表的外鍵列不能為not null
No action方式：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行update/delete操作
Restrict方式：同no action，都是立即檢查外鍵約束
Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時，子表將外鍵列設置成一個預設的值，但Innodb不能識別

如果沒有指定等級，就相當於Restrict方式。

對於外鍵約束，最好是採用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

案例略

6.8 刪除外鍵約束

-- (1)第一步先查看約束名和刪除外鍵約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';
#查看某個表的約束名
ALTER TABLE 從表名 DROP FOREIGN KEY 外鍵約束名;

-- （2）第二步查看索引名和刪除索引。（註意，只能手動刪除）
SHOW INDEX FROM 表名稱; #查看某個表的索引名

ALTER TABLE 從表名 DROP INDEX 索引名;

6.9 開發場景

問題1：如果兩個表之間有關係（一對一、一對多），比如：員工表和部門表（一對多），它們之間是否要建外鍵約束？

答：不是的

問題2：建和不建外鍵約束有什麼區別？

答：建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）會受到限制，從語法層面受到限制。例如：在員工表中不可能添加一個員工信息，它的部門的值在部門表中找不到。

不建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）不受限制，要保證數據的引用完整性，只能依靠程式員的自覺 ，或者是 在Java程式中進行限定 。例如：在員工表中，可以添加一個員工的信息，它的部門指定為一個完全不存在的部門。

問題3：那麼建和不建外鍵約束和查詢有沒有關係？

答：沒有

6.10 阿裡開發規範

7.CHECK 約束

7.1 作用

檢查某個欄位的值是否符合xx要求，一般指的是值得範圍。

7.2 關鍵字

CHECK

7.3 說明：MySQL5.7 不支持

MySQL5.7 可以使用check約束，但check約束對數據驗證沒有任何作用。添加數據時，沒有任何錯誤或警告，但是MySQL 8.0中可以使用check約束了。

舉例

CREATE TABLE temp(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT CHECK(age > 20),
    PRIMARY KEY(id)
);

-- 或者 gender CHAR(1) CHECK(gender in ('男','女'))

8.DEFAULT約束

8.1 作用

給某個欄位/某列指定預設值，一旦設置預設值，在插入數據時，如果此欄位沒有顯式賦值，則賦值為預設值。

8.2 關鍵字

DEFAULT

8.3 如何設置預設值約束

8.3.1 創建表時

create table 表名稱(
    欄位名 數據類型 primary key,
    欄位名 數據類型 unique key not null,
    欄位名 數據類型 unique key,
    欄位名 數據類型 not null default 預設值,
);

create table 表名稱(
    欄位名 數據類型 default 預設值 ,
    欄位名 數據類型 not null default 預設值,
    欄位名 數據類型 not null default 預設值,
    primary key(欄位名),
    unique key(欄位名)
);
-- 說明：預設值約束一般不在唯一鍵和主鍵列上加

舉例

CREATE TABLE test_default(
    eid INT PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    gender CHAR DEFAULT '男',
    tel CHAR(11) NOT NULL DEFAULT '' #預設是空字元串
);

mysql> DESC test_default;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid    | int         | NO   | PRI | NULL    |       |
| ename  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | YES  |     | 男      |       |
| tel    | char(11)    | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

insert into employee values(1,'汪飛','男','13700102535'); #成功

insert into employee(eid,ename) values(2,'天琪'); #成功

insert into employee(eid,ename) values(3,'二虎');
#ERROR 1062 (23000): Duplicate entry '' for key 'tel'
#如果tel有唯一性約束的話會報錯，如果tel沒有唯一性約束，可以添加成功，因為唯一性約束，要求不能有兩個相同的值，
# 這也是為什麼，建議預設值約束不要和唯一性約束和主鍵約束一起使用

8.3.2 建表後

alter table 表名稱 modify 欄位名 數據類型 default 預設值;
#如果這個欄位原來有非空約束，還想保留非空約束，那麼在加預設值約束時，還得保留非空約束，否則非空約束就被刪除了

# 同理，在給某個欄位加非空約束也一樣，如果這個欄位原來有預設值約束，你想保留預設值約束，
# 也要在modify語句中保留預設值約束，否則就刪除了
alter table 表名稱 modify 欄位名 數據類型 default 預設值 not null;

8.4 如何刪除預設值約束

-- 本質就是對列的修改，列修改時，註意是否保留非空約束和預設值約束

alter table 表名稱 modify 欄位名 數據類型 ;#刪除預設值約束，也不保留非空約束
alter table 表名稱 modify 欄位名 數據類型 not null; #刪除預設值約束，保留非空約束

alter table test modify gender char; #刪除gender欄位預設值約束，如果有非空約束，也一併刪除
alter table test modify tel char(11) not null;#刪除tel欄位預設值約束，保留非空約束

9. 面試

面試1、為什麼建表時，加not null default '' 或 default 0

不想讓表中出現null值。

面試2、為什麼不想要 null 的值

（1）不好比較。null是一種特殊值，比較時只能用專門的is null 和 is not null來比較。碰到運算符，通常返回null。

（2）效率不高。影響提高索引效果。因此，我們往往在建表時 not null default '' 或 default 0

面試3、帶AUTO_INCREMENT約束的欄位值是從1開始的嗎？

在MySQL中，預設AUTO_INCREMENT的初始值是1，每新增一條記錄，欄位值自動加1。設置自增屬性（AUTO_INCREMENT）的時候，還可以指定第一條插入記錄的自增欄位的值(ALTER TABLE 表名AUTO_INCREMENT = 100)，這樣新插入的記錄的自增欄位值從初始值開始遞增，如在表中插入第一條記錄，同時指定id值為5，則以後插入的記錄的id值就會從6開始往上增加。添加主鍵約束時，往往需要設置欄位自動增加屬性。但很少手動改變自增長的初始值，因為使用了自增長，就是希望使用自增長的規則來添加數據的。

面試4、並不是每個表都可以任意選擇存儲引擎？外鍵約束（FOREIGN KEY）不能跨引擎使用。

MySQL支持多種存儲引擎，每一個表都可以指定一個不同的存儲引擎，需要註意的是：外鍵約束是用來保證數據的參照完整性的，如果表之間需要關聯外鍵，卻指定了不同的存儲引擎，那麼這些表之間是不能創建外鍵約束的。所以說，存儲引擎的選擇也不完全是隨意的。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。