MySQL 約束（入門版）

一、約束的基本概念

二、約束演示

三、外鍵約束

（一）介紹

（二）外鍵約束語法

（三）刪除/更新行為

一、約束的基本概念

1、概念：約束是作用于表中字段上的規則，用于限制存儲在表中的數據。

2、目的：保證數據庫中數據的正確、有效性和完整性。

3、分類

4、作用

????????約束是作用于表中字段上的，可以在創建表/修改表的時候添加約束。

????????非空約束、唯一約束、主鍵約束、默認約束、檢查約束主要用于限定對應字段的數據，在DDL (數據定義語言)中使用。

????????外鍵約束用于建立兩表之間的聯系，在更新與刪除對應內容時，兩表發生對應的變化。

二、約束演示

????????上面我們介紹了數據庫中常見的約束，以及約束涉及到的關鍵字，那這些約束我們到底如何在創建表、修改表的時候來指定呢，接下來我們就通過一個案例，來演示一下。

????????案例需求：根據需求，完成表結構的創建。需求如下：

對應的建表語句為：

create table tb_user(id int auto_increment primary key comment 'ID唯一標識',name varchar(10) not null unique comment '姓名' ,age int check (age > 0 && age <= 120) comment '年齡' ,status char(1) default '1' comment '狀態',gender char(1) comment '性別'
);

????????在為字段添加約束時，我們只需要在字段之后加上約束的關鍵字即可，如果有多個約束，則使用空格隔開。如果需要 id 字段是主鍵并且自增，則可以再使用關鍵字auto_increment。

????????因為?id 列被定義為自增主鍵，所以插入數據時通常不需要顯式地為該列指定值，數據庫會自動為其生成一個唯一且遞增的值。

????????我們執行上面的SQL把表結構創建完成，然后接下來，就可以通過一組數據進行測試，從而驗證一下，約束是否可以生效。

--這兩條可以正常執行
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');--第一條因為名字為null，第二條因為名字重復，所以這兩條都不能正常執行
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');--第一條可以正常執行，第二條和第三條因為年齡超過范圍，所以不能正常執行
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');--本條可以正常執行
insert into tb_user(name,age,gender) values ('Tom5',120,'男');

三、外鍵約束

（一）介紹

1、外鍵約束的基本概念? ? ? ??

????????外鍵用來讓兩張表的數據之間建立連接，從而保證數據的一致性和完整性。接下來，我們來看一個例子。

????????左側的emp表是員工表，里面存儲員工的基本信息，包含員工的id、姓名、年齡、職位、薪資、入職日期、上級主管id、部門id。

????????右側的dept表是部門表，里面存儲部門的基本信息，包括含部門的id與名字。

? ? ? ? 在員工表emp中，部門id (dept_id)作為外鍵，關聯部門表dept的主鍵id。

????????具有外鍵的表被稱為子表，外鍵所關聯的表被稱為父表；所以上面的員工表emp是子表，而部門表dept是父表。

????????注意：目前上述兩張表，只是在邏輯上存在這樣一層關系；在數據庫層面，并未建立外鍵關聯，所以是無法保證數據的一致性和完整性的。

2、外鍵約束測試

????????沒有數據庫外鍵關聯的情況下，能否保證一致性和完整性呢，我們建表來測試一下。

create table dept(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部門名稱'
)comment '部門表';
insert into dept (id, name) values (1, '研發部'), (2, '市場部'),(3, '財務部'), (4, '銷售部'), (5, '總經辦');

create table emp(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',age int comment '年齡',job varchar(20) comment '職位',salary int comment '薪資',entrydate date comment '入職時間',managerid int comment '直屬領導ID',dept_id int comment '部門ID'
)comment '員工表';insert into emp (id, name, age, job,salary, entrydate, managerid, dept_id)
values(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),(2, '張無忌', 20, '項目經理',12500, '2005-12-05', 1,1),(3, '楊逍', 33, '開發', 8400,'2000-11-03', 2,1),(4, '韋一笑', 48, '開發',11000, '2002-02-05', 2,1),(5, '常遇春', 43, '開發',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序員鼓勵師',6600, '2004-10-12',2,1);

????????接下來，我們可以做一個測試，刪除id為1的部門信息。

? ? ? ? 結果，我們看到刪除成功之后，部門表不存在id為1的部門，而在emp表中還有很多的員工，關聯的為id為1的部門，此時就出現了數據的不完整性。 而要想解決這個問題就得通過數據庫的外鍵約束。

（二）外鍵約束語法

1、添加外鍵

（1）在建表時添加外鍵

create?table?表名(
????????字段名數據類型,
????????...
????????[ constraint ] [外鍵名稱] foreign key?(外鍵字段名) references 主表 (主表列名)?
);

????????在創建外鍵約束時，constraint 關鍵字是可選的，它的主要作用是為外鍵約束命名，方便后續對外鍵約束進行管理，比如刪除或者修改外鍵約束等操作。

????????如果不使用 constraint 關鍵字，數據庫會自動為約束生成一個默認名稱。

????????如果需要查詢這默認名稱，可以通過查詢 information_schema.table_constraints?系統表來獲取外鍵約束的名稱。以下是一個示例查詢，用于查找 employees 表的外鍵約束名稱：

select constraint_name
from information_schema.table_constraints
where table_schema = database() and table_name = 'employees'and constraint_type = 'foreign key';

? ? ? ? 查詢中，table_schema = database()用于指定當前數據庫，table_name= employees'表示要查找employees表的外鍵約束，constraint_type = 'foreign key'確保只查找外鍵約束。

（2）建表后額外添加

alter table 表名

add constraint 外鍵名稱 foreign key (外鍵字段名)?references 主表 (主表列名) ;

????????案例：為 emp 表的 dept_id 字段添加外鍵約束,關聯dept表的主鍵id。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

? ? ? ? 此時在表中可以發現，字段dept_id出現了藍色鑰匙的形狀，這是外鍵約束；字段id顯示的是黃色鑰匙的形狀，這是主鍵約束。

????????添加了外鍵約束之后，我們再到dept表(父表)刪除id為1的記錄，此時將會報錯，不能刪除或更新父表記錄，因為存在外鍵約束。

2、刪除外鍵

alter table 表名 drop foreign key 外鍵名稱;

????????案例：刪除emp表的外鍵fk_emp_dept_id。

alter table emp drop foreign key fk_emp_dept_id;

（三）增加外鍵約束后的刪除/更新行為

1、具體的刪除/更新行為

????????添加了外鍵之后，再刪除父表數據時產生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種：

? ? ? ? 如果沒有規定對應的行為，默認行為是restrict，同時no action與其作用一致。

????????cascade 的作用是級聯。如果父表的內容被刪除了，其對應子鍵所在行的數據也會被刪除。如果父表的內容被更新了，其對應子鍵也會更新對應內容。

????????在父鍵的內容被刪除時，如果有對應外鍵，set null 會將其設置成 null，set default會將其設置為一個默認的值。

2、具體的語法

alter table 表名

add constraint 外鍵名稱 foreign key (外鍵字段) references?主表名 (主表字段名)

on update (更新行為) on delete (刪除行為);

3、具體演示

????????由于 restrict 是默認行為，我們前面語法演示時，已經測試過了，就不再演示了，這里我們再演示其他的兩種行為：cascade、set null。

（1）cascade

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

① 修改父表id為1的記錄，將id修改為6

????????我們發現，原來在子表中dept_id值為1的記錄，現在也變為6了，這就是cascade級聯的效果。在一般的業務系統中，不會修改一張表的主鍵值。

② 刪除父表id為6的記錄

（2）set null

????????在進行測試之前，我們先需要刪除上面建立的外鍵 fk_emp_dept_id。然后再通過數據腳本，將emp、dept表的數據恢復了。

????????接下來，我們刪除id為1的數據，看看會發生什么樣的現象。

????????我們發現父表的記錄是可以正常的刪除的，父表的數據刪除之后，再打開子表 emp，我們發現子表emp的dept_id字段，原來dept_id為1的數據，現在都被置為null了。這就是 set null 這種刪除/更新行為的效果。
????????
????????以上即為MySQL 約束（入門版）的全部內容，創作不易，麻煩三連支持一下唄~??