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

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

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

一、約束的基本概念

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

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

3、分類

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

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

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

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

需求3：name字段長(zhǎng)度為10個(gè)字符并且不能為空。

需求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é)號(hào)"
    -> ) 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)

說(shuō)明：

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)

說(shuō)明1：各個(gè)字段都復(fù)合各個(gè)字段的要求，添加正常沒有問題

驗(yàn)證2：添加一個(gè)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

說(shuō)明2：直接報(bào)錯(cuò)，提示 name data too long 太長(zhǎng)了

驗(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.

說(shuō)明3：這里提示了一個(gè)驗(yàn)證錯(cuò)誤

驗(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)

說(shuō)明4：在上面的insert 語(yǔ)句中只設(shè)置了name,age,stu_num三個(gè)字段，所以adderss就自動(dòng)設(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'

說(shuō)明5：提示10002已經(jīng)重復(fù)了

三、外鍵約束介紹

1、什么是外鍵

首先外鍵是表中一個(gè)字段

外鍵是兩張表之間的紐帶

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

2、外鍵的介紹

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

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

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

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

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

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

3、多外鍵展示

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

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

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

4、不符合外鍵的展示

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

說(shuō)明2：但是這個(gè)班級(jí)評(píng)級(jí)字段就不存在外鍵的特征，因?yàn)榘嗉?jí)評(píng)級(jí)在《班級(jí)考核與評(píng)級(jí)對(duì)照表》中不是惟一的。

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

四、外鍵約束展示

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、添加外鍵的語(yǔ)法

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

說(shuō)明1：alter table 是DML語(yǔ)法，修改表的意思，在之前的文章中已經(jīng)介紹過

說(shuō)明2：add constraint 是添加約束的意思

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

說(shuō)明4：references 后面跟上父表和父表中字段

4、需求：給student表中的teacher_id設(shè)置為teacher表的外鍵，并且對(duì)應(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

說(shuō)明1：外鍵一旦設(shè)置成功，將會(huì)保持子表和父表的數(shù)據(jù)一致性和完整性。

說(shuō)明2：這個(gè)時(shí)候，如果我刪除《teacher》表中的id=1的張三老師，就會(huì)出錯(cuò)，因?yàn)?，如果張三在《teacher》表中刪除了，則在《student》中的輔導(dǎo)員編號(hào)這列數(shù)據(jù)就找不到對(duì)應(yīng)的值

說(shuō)明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`))

說(shuō)明1：這個(gè)時(shí)候就會(huì)提示，不能刪除或者修改父表中的數(shù)據(jù)，因?yàn)橛型怄I存在

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

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

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

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

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

set default：父表有變更時(shí)，子表將外鍵列設(shè)置成一個(gè)默認(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級(jí)聯(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)

說(shuō)明1：這個(gè)時(shí)候我們會(huì)發(fā)現(xiàn)，當(dāng)我修改了《teacher》表中輔導(dǎo)員編號(hà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)

說(shuō)明2：和更新一樣，cascade的刪除也是級(jí)聯(lián)的。

8、驗(yàn)證set null的更新和刪除行為

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

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)

說(shuō)明1：對(duì)應(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)

說(shuō)明1：當(dāng)刪除數(shù)據(jù)的時(shí)候，子表中對(duì)應(yīng)的外鍵數(shù)據(jù)也會(huì)變成了null

五、刪除外鍵約束

1、刪除外鍵的語(yǔ)法

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)

說(shuō)明1：這個(gè)時(shí)候就可以在《teacher》表中刪除id=1的張三老師了，但是這樣《student》表和《teacher》表的數(shù)據(jù)的一致性就破壞了

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

您可能感興趣的文章: