Mysql基礎7-約束 - tw511教學網

一、約束的基本概念

　　1、概念：約束是作用於表中欄位上的規則，用於限制儲存在表中的資料

　　2、目的：保證資料庫中的資料的正確性，有效性和完整性

　　3、分類

- 非空約束(not null)：限制該欄位的資料不能為null
- 唯一約束(unique)：保證該欄位的所有資料都是唯一，不重複的
- 主鍵約束(primary key)：主鍵是一行資料的唯一標識，要求非空且唯一
- 預設約束(default)：儲存資料時，如果未指定該欄位的值，則採用預設值
- 檢查約束(check 8.0以後的新約束)：保證欄位滿足某一個條件
- 外來鍵約束(foreign key)：用來讓兩張變的資料建立連線，保證資料的一致性和完整性

二、約束的案例實踐

　　需求1：建立一個表id、name、age、address、stu_num五個欄位。

　　需求2：id欄位為主鍵，且設定為自動遞增。

　　需求3：name欄位長度為10個字元並且不能為空。

　　需求4：age欄位要大於0並且小於150.

　　需求5：address欄位如果不設，預設為廣州。

　　需求6：stu_num唯一且不能為空。

mysql> create table stu_table(
    -> id int primary key auto_increment comment "id主鍵",
    -> name varchar(10) not null comment "姓名",
    -> age int check(age>0 && age<150) comment "年齡",
    -> address varchar(10) default "廣州" comment "地址",
    -> stu_num int not null unique comment "學號"
    -> ) comment "學生表";
Query OK, 0 rows affected, 1 warning (0.03 sec)

　　stu_table的表結構如下

mysql> desc stu_table;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| age     | int         | YES  |     | NULL    |                |
| address | varchar(10) | YES  |     | 廣州    |                |
| stu_num | int         | NO   | UNI | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

　　說明：

- Type 是欄位的型別
- Null 是是否允許為空
- key 是標記主鍵，外來鍵和唯一的
- Default 是該欄位的預設值
- Extra 是一些額外資訊的展示

　　驗證1：新增一組正常資料

mysql> insert into stu_table (name, age, address, stu_num) values ("張三", 18, "深圳",10001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 張三   |   18 | 深圳    |   10001 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)

　　說明1：各個欄位都複合各個欄位的要求，新增正常沒有問題

　　驗證2：新增一個name 超過10位的異常資料

mysql> insert into stu_table (name, age, address, stu_num) values ("ABCDEFGHIJK", 18, "深圳",10002);
ERROR 1406 (22001): Data too long for column 'name' at row 1

　　說明2：直接報錯，提示 name data too long 太長了

　　驗證3：驗證age 大於150的異常情況

mysql> insert into stu_table (name, age, address, stu_num) values ("李四", 152, "深圳",10003);
ERROR 3819 (HY000): Check constraint 'stu_table_chk_1' is violated.

　　說明3：這裡提示了一個驗證錯誤

　　驗證4：驗證address不填寫，預設值的設定

mysql> insert into stu_table (name, age, stu_num) values ("李四", 19,10002);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 張三   |   18 | 深圳    |   10001 |
|  2 | 李四   |   19 | 廣州    |   10002 |
+----+--------+------+---------+---------+
2 rows in set (0.00 sec)

　　說明4：在上面的insert 語句中只設定了name,age,stu_num三個欄位，所以adderss就自動設定了預設值廣州

　　驗證5：驗證stu_num欄位的唯一性

mysql> insert into stu_table (name, age, address, stu_num) values ("王五", 21, "上海",10002);
ERROR 1062 (23000): Duplicate entry '10002' for key 'stu_table.stu_num'

　　說明5：提示10002已經重複了

三、外來鍵約束介紹

　　1、什麼是外來鍵

- 首先外來鍵是表中一個欄位
- 外來鍵是兩張表之間的紐帶
- 設定外來鍵的表稱之為子表，外來鍵對應的表稱之為父表

　　2、外來鍵的介紹

　　　　說明1：《學生表》和《輔導員》表示兩張相互獨立的表。

　　　　說明2：在《學生表》中的輔導員編號，和《輔導員表》中的輔導員編號是一一對應的

　　　　說明3：這種情況下就可以通過輔導員編號這個欄位將《學生表》和《輔導員表》聯絡起來了

　　　　說明4：這是輔導員編號欄位，就符合設定為外來鍵的條件

　　　　說明5：如果將《學生表》中的輔導員編號欄位設定為外來鍵，則《學生表》為子表，《輔導員表》為父表

　　　　說明6：外來鍵在父表中是唯一，不可重複的。

　　3、多外來鍵展示

　　　　說明1：通過上圖發現《學生表》中的班級id和《班級表》中的班級id也存在一一對應的關係

　　　　說明2：班級id也符合設定外來鍵的標準。

　　　　說明3：例如：輔導員編號，班級id都符合外來鍵的設定標準，所以一個表中可以有多個外來鍵，但是每個外來鍵對應不同的表

　　4、不符合外來鍵的展示

　　　　說明1：在《學生表》班級評級欄位和《班級考核與平級對照表》中的班級平級欄位也存在著關係。

　　　　說明2：但是這個班級評級欄位就不存在外來鍵的特徵，因為班級評級在《班級考核與評級對照表》中不是惟一的。

　　　　說明3：在子表中的四星，對應父表中有三種情況這樣就會出現子表中的四星到底對應父表的哪一個四星的情況。

四、外來鍵約束展示

　　1、原始資料：student表結構及其資料

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

　　2、原始資料：teacher表結構及其資料

mysql> select * from teacher;
+------------+--------------+
|         id | teacher_name |
+------------+--------------+
|          1 | 張三         |
|          2 | 李四         |
|          3 | 王五         |
+------------+--------------+
3 rows in set (0.00 sec)

　　3、新增外來鍵的語法

alter table 表名 add constraint 外來鍵名稱 foreign key (外來鍵欄位名) references 父表 (父表欄位）on update 更新行為 on delete 刪除行為

　　　　說明1：alter table 是DML語法，修改表的意思，在之前的文章中已經介紹過

　　　　說明2：add constraint 是新增約束的意思

　　　　說明3：foreign key 是外來鍵約束的關鍵字

　　　　說明4：references 後面跟上父表和父表中欄位

　　4、需求：給student表中的teacher_id設定為teacher表的外來鍵，並且對應id欄位的資料

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher (id);
Query OK, 6 rows affected (0.22 sec)
Records: 6  Duplicates: 0  Warnings: 0

　　　　說明1：外來鍵一旦設定成功，將會保持子表和父表的資料一致性和完整性。

　　　　說明2：這個時候，如果我刪除《teacher》表中的id=1的張三老師，就會出錯，因為，如果張三在《teacher》表中刪除了，則在《student》中的輔導員編號這列資料就找不到對應的值

　　　　說明3：從而這樣就破壞了資料的完整性和一致性

mysql> delete from teacher where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql_test`.`student`, CONSTRAINT `fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`))

　　　　說明1：這個時候就會提示，不能刪除或者修改父表中的資料，因為有外來鍵存在

　　5、外來鍵資料的更新和刪除行為

- no action：當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應的外來鍵，如果有則不允許刪除/更新(與restrict一致)
- restrict：當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應的外來鍵，如果有則不允許刪除/更新(與 no action一致)
- cascade：當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應的外來鍵，如果有，則也刪除/更新外來鍵在子表中的記錄
- set null：當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應的外來鍵，如果有則設定子表中該外來鍵的值為null,這就要求該外來鍵記錄允許null
- set default：父表有變更時，子表將外來鍵列設定成一個預設的值(Innodb不支援)

　　6、重新建立《student》和《teacher》表並新增外來鍵

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 張三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update cascade on delete cascade;
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

　　7、驗證cascade級聯行為

　　　　驗證1：我修改《teacher》表中id=1的資料改為id=4

mysql> update teacher set id=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 張三         |
+----+--------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          4 |    98 |
|  2 | stu2     |          4 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

　　　　說明1：這個時候我們會發現，當我修改了《teacher》表中輔導員編號的id欄位是，在《student》表中teacher_id 原本等於1的也都改為了4，這就是cascade的作用

　　　　驗證2：cascade的刪除行為

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
4 rows in set (0.00 sec)

　　　　說明2：和更新一樣，cascade的刪除也是級聯的。

　　8、驗證set null的更新和刪除行為

　　　　需求1：同樣先刪除《student》和《teacher》表然後重新建立新的表，重新建立外來鍵約束測試

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 張三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update set null on delete set null;
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

　　　　驗證1：更新《teacher》表中id=1的資料，改為id=4

mysql> update teacher set id=4 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 張三         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.01 sec)

　　　　說明1：對應更新的資料都改為了null

　　　　驗證2：刪除《teacher》表中id=2的資料

mysql> delete from teacher where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  3 | 王五         |
|  4 | 張三         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |       NULL |    79 |
|  4 | stu4     |       NULL |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

　　　　說明1：當刪除資料的時候，子表中對應的外來鍵資料也會變成了null

五、刪除外來鍵約束

　　1、刪除外來鍵的語法

alter table 表名 drop foreign key 外來鍵名稱；

mysql> alter table student drop foreign key fk_teacher;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　2、刪除外來鍵後，資料一致性和完整性的驗證

mysql> delete from teacher where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

　　　　說明1：這個時候就可以在《teacher》表中刪除id=1的張三老師了，但是這樣《student》表和《teacher》表的資料的一致性就破壞了