MySQL基礎(chǔ)學(xué)習(xí)之約束詳解

更新時間：2023年07月20日 09:11:09 作者：Se7eN_HOU

約束是作用于表中字段上的規(guī)則，用于限制儲存在表中的數(shù)據(jù)，這篇文章主要為大家介紹了MySQL中約束的案例以及外鍵約束的展示與刪除，需要的可以參考一下

一、約束的基本概念

1、概念：約束是作用于表中字段上的規(guī)則，用于限制儲存在表中的數(shù)據(jù)

2、目的：保證數(shù)據(jù)庫中的數(shù)據(jù)的正確性，有效性和完整性

3、分類

非空約束(not null)：限制該字段的數(shù)據(jù)不能為null
唯一約束(unique)：保證該字段的所有數(shù)據(jù)都是唯一，不重復(fù)的
主鍵約束(primary key)：主鍵是一行數(shù)據(jù)的唯一標(biāo)識，要求非空且唯一
默認(rèn)約束(default)：保存數(shù)據(jù)時，如果未指定該字段的值，則采用默認(rèn)值
檢查約束(check 8.0以后的新約束)：保證字段滿足某一個條件
外鍵約束(foreign key)：用來讓兩張變的數(shù)據(jù)建立連接，保證數(shù)據(jù)的一致性和完整性

二、約束的案例實(shí)踐

需求1：創(chuàng)建一個表id、name、age、address、stu_num五個字段。

需求2：id字段為主鍵，且設(shè)置為自動遞增。

需求3：name字段長度為10個字符并且不能為空。

需求4：age字段要大于0并且小于150.

需求5：address字段如果不設(shè)，默認(rèn)為廣州。

需求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 "學(xué)號"
    -> ) comment "學(xué)生表";
Query OK, 0 rows affected, 1 warning (0.03 sec)

stu_table的表結(jié)構(gòu)如下

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 是標(biāo)記主鍵，外鍵和唯一的
Default 是該字段的默認(rèn)值
Extra 是一些額外信息的展示

驗(yàn)證1：添加一組正常數(shù)據(jù)

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：各個字段都復(fù)合各個字段的要求，添加正常沒有問題

驗(yàn)證2：添加一個name 超過10位的異常數(shù)據(jù)

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 太長了

驗(yàn)證3：驗(yàn)證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：這里提示了一個驗(yàn)證錯誤

驗(yàn)證4：驗(yàn)證address不填寫，默認(rèn)值的設(shè)置

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 語句中只設(shè)置了name,age,stu_num三個字段，所以adderss就自動設(shè)置了默認(rèn)值廣州

驗(yàn)證5：驗(yàn)證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已經(jīng)重復(fù)了

三、外鍵約束介紹

1、什么是外鍵

首先外鍵是表中一個字段

外鍵是兩張表之間的紐帶

設(shè)置外鍵的表稱之為子表，外鍵對應(yīng)的表稱之為父表

2、外鍵的介紹

說明1：《學(xué)生表》和《輔導(dǎo)員》表示兩張相互獨(dú)立的表。

說明2：在《學(xué)生表》中的輔導(dǎo)員編號，和《輔導(dǎo)員表》中的輔導(dǎo)員編號是一一對應(yīng)的

說明3：這種情況下就可以通過輔導(dǎo)員編號這個字段將《學(xué)生表》和《輔導(dǎo)員表》聯(lián)系起來了

說明4：這是輔導(dǎo)員編號字段，就符合設(shè)置為外鍵的條件

說明5：如果將《學(xué)生表》中的輔導(dǎo)員編號字段設(shè)置為外鍵，則《學(xué)生表》為子表，《輔導(dǎo)員表》為父表

說明6：外鍵在父表中是唯一，不可重復(fù)的。

3、多外鍵展示

說明1：通過上圖發(fā)現(xiàn)《學(xué)生表》中的班級id和《班級表》中的班級id也存在一一對應(yīng)的關(guān)系

說明2：班級id也符合設(shè)置外鍵的標(biāo)準(zhǔn)。

說明3：例如：輔導(dǎo)員編號，班級id都符合外鍵的設(shè)置標(biāo)準(zhǔn)，所以一個表中可以有多個外鍵，但是每個外鍵對應(yīng)不同的表

4、不符合外鍵的展示

說明1：在《學(xué)生表》班級評級字段和《班級考核與平級對照表》中的班級平級字段也存在著關(guān)系。

說明2：但是這個班級評級字段就不存在外鍵的特征，因?yàn)榘嗉壴u級在《班級考核與評級對照表》中不是惟一的。

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

四、外鍵約束展示

1、原始數(shù)據(jù)：student表結(jié)構(gòu)及其數(shù)據(jù)

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、原始數(shù)據(jù)：teacher表結(jié)構(gòu)及其數(shù)據(jù)

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語法，修改表的意思，在之前的文章中已經(jīng)介紹過

說明2：add constraint 是添加約束的意思

說明3：foreign key 是外鍵約束的關(guān)鍵字

說明4：references 后面跟上父表和父表中字段

4、需求：給student表中的teacher_id設(shè)置為teacher表的外鍵，并且對應(yīng)id字段的數(shù)據(jù)

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：外鍵一旦設(shè)置成功，將會保持子表和父表的數(shù)據(jù)一致性和完整性。

說明2：這個時候，如果我刪除《teacher》表中的id=1的張三老師，就會出錯，因?yàn)椋绻麖埲凇秚eacher》表中刪除了，則在《student》中的輔導(dǎo)員編號這列數(shù)據(jù)就找不到對應(yīng)的值

說明3：從而這樣就破壞了數(shù)據(jù)的完整性和一致性

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：這個時候就會提示，不能刪除或者修改父表中的數(shù)據(jù)，因?yàn)橛型怄I存在

5、外鍵數(shù)據(jù)的更新和刪除行為

no action：當(dāng)在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)的外鍵，如果有則不允許刪除/更新(與restrict一致)

restrict：當(dāng)在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)的外鍵，如果有則不允許刪除/更新(與 no action一致)

cascade：當(dāng)在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)的外鍵，如果有，則也刪除/更新外鍵在子表中的記錄

set null：當(dāng)在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)的外鍵，如果有則設(shè)置子表中該外鍵的值為null,這就要求該外鍵記錄允許null

set default：父表有變更時，子表將外鍵列設(shè)置成一個默認(rèn)的值(Innodb不支持)

6、重新創(chuàng)建《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、驗(yàn)證cascade級聯(lián)行為

驗(yàn)證1：我修改《teacher》表中id=1的數(shù)據(jù)改為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：這個時候我們會發(fā)現(xiàn)，當(dāng)我修改了《teacher》表中輔導(dǎo)員編號的id字段是，在《student》表中teacher_id 原本等于1的也都改為了4，這就是cascade的作用

驗(yàn)證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的刪除也是級聯(lián)的。

8、驗(yàn)證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

驗(yàn)證1：更新《teacher》表中id=1的數(shù)據(jù)，改為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：對應(yīng)更新的數(shù)據(jù)都改為了null

驗(yàn)證2：刪除《teacher》表中id=2的數(shù)據(jù)

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：當(dāng)刪除數(shù)據(jù)的時候，子表中對應(yīng)的外鍵數(shù)據(jù)也會變成了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、刪除外鍵后，數(shù)據(jù)一致性和完整性的驗(yàn)證

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》表的數(shù)據(jù)的一致性就破壞了

到此這篇關(guān)于MySQL基礎(chǔ)學(xué)習(xí)之約束詳解的文章就介紹到這了,更多相關(guān)MySQL約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: