MySQL中的約束Constraint解讀

更新時間：2025年01月13日 08:51:14 作者：淺念同學

這篇文章詳細介紹了MySQL中的各種約束類型,包括非空約束、默認約束、檢查約束、唯一約束和主鍵約束,以及外鍵約束,每種約束類型都有其特定的用途和語法,并且可以是列級約束或表級約束,文章還探討了如何給約束命名、刪除約束

約束簡介

約束是我們在創(chuàng)建表的時候, 我們可以給表中的字段添加約束確保我們的數(shù)據(jù)的完整性和有效性, 比如大家平時上網(wǎng)時注冊用戶常見的 : 用戶名不能為空, 對不起, 用戶名已經(jīng)存在等提示信息

約束通常包括下面的這6種

約束類型	約束關(guān)鍵字
非空約束	not null
默認約束	default
檢查約束	check
唯一約束	unique
主鍵約束	primary key
外鍵約束	foreign key

下面我們會詳細的剖析這幾種約束(默認約束省略, 就是一個簡單的在不插入這個字段的數(shù)據(jù)時插入默認值)

非空約束

這個約束比較好理解, 就是插入的數(shù)據(jù)不能為空, 當我們設(shè)置這一個約束的時候, 我們使用desc展示表結(jié)構(gòu)的時候, 表的Null那一行就會設(shè)置為NO, 允許為空就會為YES, 下面是我們的一個實例

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

嘗試完成下面的一個需求, 創(chuàng)建一個學校表, 有編號, 學校名稱(不能為空), 建校時間, 這時候就需要給name字段加上一個非空約束not null

我們嘗試執(zhí)行下面的SQL語句

-- 創(chuàng)建一個學校表
create table school(
    sno int comment '學校編號',
    name varchar(255) not null comment '學校名稱',
    est_time date comment '建校時間'
);

-- 插入幾條數(shù)據(jù)來進行測試(關(guān)于日期類型的插入, 其實底層會進行str_to_date函數(shù)的調(diào)用, 把字符串轉(zhuǎn)化為日期)
insert into school (sno, name, est_time) values (1, '北京101中學', '1910-08-11');
insert into school (sno, name, est_time) values (2, '南開附中', '1912-04-13');
insert into school (sno, name, est_time) values (3, '清華附中', '1915-03-12');

-- select 查詢一下當前的信息
select * from school;

-- 執(zhí)行結(jié)果如下
+------+-----------------+------------+
| sno  | name            | est_time   |
+------+-----------------+------------+
|    1 | 北京101中學     | 1910-08-11 |
|    2 | 南開附中        | 1912-04-13 |
|    3 | 清華附中        | 1915-03-12 |
+------+-----------------+------------+

由于我們添加了非空約束, 也就是我們的name不可以為null, 如果插入一條數(shù)據(jù)沒有name就會報錯

-- 嘗試執(zhí)行下面的SQL
insert into school (sno, est_time) values (4, '1899-11-06');
-- 會直接報錯, 報錯信息如下
ERROR 1364 (HY000): Field 'name' doesn't have a default value

檢查約束

其實就是在創(chuàng)建一張表的時候添加一定的檢查信息, 這個約束時MySQL8版本之后新添加的, 在先前的版本中是不存在的

基礎(chǔ)語法

create table [表名](
	...字段信息
	check(約束條件)
);

我們嘗試建一張學生表, 要求添加學生的年齡必須大于18歲, 這種情況就可以使用檢查約束

-- 創(chuàng)建一張學生表
create table t_stu(
    sno int,
    name varchar(255),
    age int,
    check(age > 18)
);

-- 執(zhí)行DML語句
insert into t_stu(sno, name, age) values (1, 'hh', 19);
insert into t_stu(sno, name, age) values (2, 'xx', 18);
-- 第一條是執(zhí)行成功的, 但是第二條 18 == 18, 不滿足check約束, 所以失敗

唯一約束

對一個字段添加unique約束, 這個字段就具有了唯一性, 唯一性的字段是可以為null, 但不可以重復, 如果是null的話, 是可以重復的, 我們拿下面的t_stu表作為一個例子說明

# 創(chuàng)建了一個學生表, 這個表的email字段是唯一的, 不可以重復
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255) unique
);
# 我們嘗試執(zhí)行下面的DML語句, 執(zhí)行成功成功的標明 √, 否則為 ×
insert into t_stu (no, name, email) values (1, 'hh', 'hh@163.com');
insert into t_stu (no, name, email) values (2, 'xx', 'xx@163.com');
insert into t_stu (no, name) values (3, 'xx'), (4, 'wx');
# 查看一下當前的信息
select * from t_stu;
+------+------+------------+
| no   | name | email      |
+------+------+------------+
|    1 | hh   | hh@163.com |
|    2 | xx   | xx@163.com |
|    3 | xx   | NULL       |
|    4 | wx   | NULL       |
+------+------+------------+
# 嘗試插入一條重復的數(shù)據(jù)
insert into t_stu (no, name, email) values (5, 'sf', 'hh@163.com');
# 直接報錯, 因為email的位置重復了

unique約束也可以和not null 同時使用表示非空且唯一

create table t_stu(
	no int,
	name varchar(255),
	email varchar(255) not null unique
);

列級約束與表級約束

如果一個約束緊緊的跟在字段后面, 那這個字段僅僅作用于這一個字段, 我們稱之為列級約束, 如果一個約束位于表定義的結(jié)尾位置, 那么這個約束我們稱之為表級約束, 這種約束可以約束多種字段

unique, primary key, foreign key 可以作為表級約束存在, not null 不可以作為表級約束

我們比較一下下面的SQL語句

# unique的列級約束
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255),
	unique(email)
);
# unique的表級約束
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255),
	unique(name,email)
);

那么上述列級和表級的約束有什么區(qū)別呢?

答案是 : 表級可以聯(lián)合聯(lián)合多個字段而列級只能約束一個字段

給約束起名字

約束是以對象的形式存在的, 所有的約束對象對象都存在一個系統(tǒng)表中

information_schema(四個系統(tǒng)數(shù)據(jù)庫之一)中的table_constraints這個表中, 這張表保存的所有的約束名稱信息

這里注意, 列級約束是不能起名字的, 但是有系統(tǒng)默認分配的名字, 只有表級別才可以起名字, 如果不起名字的話, 也會自動分配一個

找到約束的名字我們就可以對約束進行刪除, 從而消除對某些字段的約束

首先找到這張表

使用這個information_schema數(shù)據(jù)庫

這里面有79張表, 找到這個TABLE_CONSTRAINTS表, 這個表存儲的所有的約束對象的信息

desc查看一下表結(jié)構(gòu), 我們找到剛才的 t_stu 學生表的約束名稱

先用DDL語句查看一下創(chuàng)建 t_stu 這張表時的建表語句

我們可以看到, 我們創(chuàng)建表的時候?qū)mail字段進行了unique約束, 但是沒有給約束起名字, 所以系統(tǒng)會自動分配名字

下面我們查看一下這個約束的名稱

這里可以看到這個約束的名稱時email

下面我們創(chuàng)建一張新的表, 從新添加一個約束并給約束起名字

基礎(chǔ)的語法如下

# 表級約束起名的語法
constraint [約束名稱] [表級約束的主體];
# 起約束名的標準
表名_約束的字段_約束的簡稱(unique/pk/fk)

創(chuàng)建一個班級表進行測試

-- 創(chuàng)建一個班級表(設(shè)置班級編號為主鍵, 班級名稱為唯一鍵)
create table class(
    cno int comment '班級編號',
    cname varchar(255) comment '班級名稱',
    constraint class_cno_pk primary key(cno),
    constraint class_cname_unique unique(cname)
);

用上面我們找到約束的名稱的流程進行演示…

# 使用一下這個系統(tǒng)庫
use information_schema;
# 找到class表的約束名稱
select constraint_name from table_constraints where table_name = 'class';

這里可能會有疑問為什么給主鍵起的名字沒有生效呢?

下面是我查閱的資料

MySQL版本限制:從MySQL8.0版本開始，主鍵的名字不再可以直接修改。這是由于 MySQL的內(nèi)部存儲引擎(如InnoDB)實現(xiàn)方式導致的，InnoDB存儲引擎中主鍵的名字是以索引的形式存儲的，修改主鍵名字實際上是修改索引的名字，會對存儲引擎的內(nèi)部數(shù)據(jù)結(jié)構(gòu)產(chǎn)生影響，因此MySQL禁止直接修改主鍵的名字。

但是經(jīng)過測試, 給外鍵起名字是生效的

主鍵約束

主鍵概念以及注意事項

主鍵約束是一個比較重要的內(nèi)容

主鍵 : primary key, 簡稱pk
主鍵約束的字段不能為NULL, 并且不可以重復
任何一張表都應該有主鍵(第一范式), 沒有主鍵的表可以被視為無效表
主鍵值是這行記錄的身份證號，是唯一標識。在數(shù)據(jù)庫表中即使兩條數(shù)據(jù)一模一樣，但由于主鍵值不同，我們也會認為是兩條完全的不同的數(shù)據(jù)。
主鍵分類：

單一主鍵(建議使用這種方式)

create table t_student(
	id bigint primary key,
	sno varchar(255) unique,
	sname varchar(255) not null
)

聯(lián)合主鍵(很少用, 了解)

create table t_user(
	no int,
	name varchar(255),
	age int,
	primary key(no,name)
);

主鍵自增

既然主鍵值是一個自然的數(shù)字，mysql為主鍵值提供了一種自增機制，不需要我們程序員維護，mysql自動維護該字段鍵自增

create table t_vip(
	no int primary key auto_increment,
	name varchar(255)
);

外鍵約束

外鍵概念以及注意事項

外鍵約束

外鍵約束 : foreign key, 簡稱fk
添加了外鍵約束的字段的數(shù)據(jù)必須來源于其他的其他字段, 不可以隨便設(shè)置
比如我們給a字段添加了外鍵約束, 要求a字段中的數(shù)據(jù)必須來源于b字段b字段不一定是主鍵, 但一定具有唯一性
外鍵約束可以給單個字段添加，叫做單一外鍵。也可以給多個字段聯(lián)合添加，叫做復合外鍵。復合外鍵很少用。
如果a表引用了b表的數(shù)據(jù)(a是外鍵, b具有唯一性被引用)

操作類型	執(zhí)行順序
創(chuàng)建表時	先創(chuàng)建b, 再創(chuàng)建a
插入數(shù)據(jù)時	先插入b, 再插入a
刪除數(shù)據(jù)時	先刪除a, 再刪除b
刪除表時	先刪除a, 再刪除b

外鍵使用場景

有下面一個需求, 我們想要創(chuàng)建一個學生表, 能夠存儲學生的信息

我們的第一種設(shè)計是這樣的

很明顯, 這樣創(chuàng)建表的方法會導致數(shù)據(jù)冗余(實質(zhì)上是違法了我們第三范式)

這張表是一種典型的一對多的情況, 所以根據(jù)創(chuàng)建表的設(shè)計(后面會講)我們創(chuàng)建為兩張表

一張學生表, 單獨存儲學生的信息, 另一張是學校表, 專門存儲學校的相關(guān)信息, 返回用外鍵進行關(guān)聯(lián)

為什么要設(shè)置外鍵約束, 因為對于學生來說, 學校編號這一個條件不是隨便設(shè)置的, 要用學校表中的信息進行約束

有了上面的鋪墊, 我們嘗試創(chuàng)建一個學生表, 和一個學校表

-- 創(chuàng)建一個學校表(把學校編號設(shè)置為主鍵)
create table t_school(
    sch_no int primary key auto_increment,
    sch_name varchar(255) unique
);

-- 創(chuàng)建一個學生表(把學生編號設(shè)置為主鍵, 然后名字添加default約束, 年齡添加check約束, 學校名稱為外鍵)
create table t_student(
    stu_no int primary key auto_increment,
    stu_name varchar(255) default '無名氏',
    age int,
    sch_no int,
    check(age >= 18),
    constraint t_student_sch_no_fk foreign key(sch_no) references t_school(sch_no)
);

-- 插入幾條測試數(shù)據(jù)(按照我們舉出來的例子)
insert into t_school (sch_name) values ('清華大學'), ('北京大學'), ('浙江大學'), ('復旦大學');
insert into t_student (stu_name, age, sch_no) values ('hxh', 19, 1), ('dwv', 18, 1), ('cac', 19, 1), ('fqe', 20, 1);
insert into t_student (stu_name, age, sch_no) values ('ger', 18, 2), ('he', 21, 2);
insert into t_student (stu_name, age, sch_no) values ('few', 20, 3), ('rhr', 22, 3), ('wgh', 22, 3);
insert into t_student (stu_name, age, sch_no) values ('rhre', 22, 4), ('wg', 21, 4);

約束的刪除與添加

由于我們下面要介紹級聯(lián)的相關(guān)操作, 所以要刪除之前的外鍵約束條件, 我們之前就說過, 可以通過找到約束的名字從而刪除約束, 這種操作其實是DDL, 對表層面的一種操作, 我們首先找到t_student表的約束的名字

刪除約束的基礎(chǔ)語法如下

# 基礎(chǔ)語法就是
alter table [表名] drop constraint [約束名稱];
# 刪除學生表的外鍵約束
alter table t_student constraint t_student_sch_no_fp;

約束添加的語法與刪除的語法類似 , 都是DDL語句那一套邏輯

# 基礎(chǔ)語法
alter table [表名] add constraint [約束名稱] [約束主體];
# 比如我們從新把剛才刪除外鍵約束添加回來
alter table t_student add constraint stu_pk
foreign key(sch_no) references t_school(sch_no);

級聯(lián)相關(guān)操作

我們上面介紹外鍵概念的時候提到過, 如果刪除表或者刪除表中的數(shù)據(jù)的時候要注意先后的順序

那有沒有一種方法, 可以在操縱主表的同時, 同時修改副表里面關(guān)聯(lián)的數(shù)據(jù)呢

級聯(lián)刪除(on delete cascade)

我們從新把上面創(chuàng)建的學校表和學生表拿出來

現(xiàn)在存在外鍵約束, 我們直接刪除學校編號為4的復旦大學是不能操作的, 因為有副表的數(shù)據(jù)引用

但是通過級聯(lián)刪除就可以實現(xiàn)這一效果(刪除主表信息的同時刪除副表內(nèi)容)

基礎(chǔ)語法

-- 只需要在創(chuàng)建約束的時候在末端加上一個 (on delete cascade)
[創(chuàng)建約束主體] on delete cascade;

下面我們進行測試

-- 首先刪除一下之前的外鍵約束
alter table t_student drop constraint stu_pk;
-- 創(chuàng)建一個新的外鍵約束(并加上一個級聯(lián)刪除的信息)
alter table t_student add constraint t_student_sch_no_fk foreign key(
sch_no) references t_school(sch_no) on delete cascade;
-- 下面我們從新進行測試
delete from t_school where sch_no = 4;
-- 此時就會顯示執(zhí)行成功, 我們從新查看一下學生表中的數(shù)據(jù), 執(zhí)行結(jié)果如下圖

注意, 級聯(lián)刪除其實是一個相當危險的操作

級聯(lián)更新(on update cascade)

作用就是當更新主表數(shù)據(jù)的同時修改副表中的相關(guān)數(shù)據(jù)

基礎(chǔ)語法

[約束主體] on update cascade;

下面是測試內(nèi)容

-- 首先還是一下上一個約束
alter table t_student drop constraint t_student_sch_no_fk;
-- 創(chuàng)建一個級聯(lián)更新約束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on update cascade;
-- 把學校編號為3改為學校編號為5
update t_school set sch_no = 5 where sch_no = 3;
-- 查看一下當前的學生信息
select * from t_student;
-- 執(zhí)行結(jié)果見下圖

級聯(lián)置空(on delete set null)

作用就是當在主表中刪除一個數(shù)據(jù)的時候, 副表中跟這條數(shù)據(jù)關(guān)聯(lián)的外鍵位置設(shè)置為NULL

基礎(chǔ)語法

[約束主體] on delete set null;

下面是我們的測試代碼

--還是先刪除約束
alter table t_student drop constraint t_student_sch_no_fk;
-- 加一個級聯(lián)置空的約束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on delete set null;
-- 上一個級聯(lián)更新我們不是3編號更新為5了么, 我們現(xiàn)在刪除5數(shù)據(jù)對應的編號學校
delete from t_school where sch_no = 5;
-- 執(zhí)行結(jié)果見下圖