Mysql基礎7-約束_ZenDei技術網路在線

一、約束的基本概念 1、概念：約束是作用於表中欄位上的規則，用於限制儲存在表中的數據 2、目的：保證資料庫中的數據的正確性，有效性和完整性 3、分類非空約束(not null)：限制該欄位的數據不能為null 唯一約束(unique)：保證該欄位的所有數據都是唯一，不重覆的主鍵約束(primar ...

一、約束的基本概念

　　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 |
|