快捷導(dǎo)航

一文帶你了解MySQL之約束

更新時(shí)間：2023年06月04日 09:49:47 作者：橙?子_

在SQL標(biāo)準(zhǔn)中，一共規(guī)定了6種不同的約束，包括非空約束，唯一約束和檢查約束等，而在MySQL中是不支持檢查約束的，所以這篇文章先對(duì)其余5種約束做一個(gè)詳解和練習(xí),需要的朋友可以參考下

1. 約束的概念

約束是作用于表中列上的規(guī)則，用于限制加入表的數(shù)據(jù)。例如，作為主鍵的列一定是非空的唯一的，否則將無(wú)法對(duì)數(shù)據(jù)進(jìn)行區(qū)分。約束的存在保證了數(shù)據(jù)庫(kù)中數(shù)據(jù)的正確性、有效性和完整性。所以約束在數(shù)據(jù)庫(kù)設(shè)計(jì)中是非常重要的。

2. 約束的分類

前面說(shuō)到SQL標(biāo)準(zhǔn)把約束分為了6大類，分別是非空約束，唯一約束，主鍵約束，檢查約束，默認(rèn)約束和外鍵約束，添加約束時(shí)我們只需要在SQL中添加關(guān)鍵詞，便可以限制表中的數(shù)據(jù)。

約束類型	功能
非空約束 NOT NULL	保證列中所有的數(shù)據(jù)不能有null值
唯一約束 UNIQUE	保證列中所有數(shù)據(jù)各不相同
主鍵約束 PRIMARY KEY	主鍵是一行數(shù)據(jù)的唯一標(biāo)識(shí)，要求非空且唯一
檢查約束 CHECK	保證列中的值滿足某一條件
默認(rèn)約束 DEFAULT	保存數(shù)據(jù)時(shí)，未指定值則采用默認(rèn)值
外鍵約束 FOREIGN KEY	外鍵用來(lái)讓兩個(gè)表的數(shù)據(jù)之間建立鏈接，保證數(shù)據(jù)的一致性和完整性

3. 非空約束

目的：保證列中所有的數(shù)據(jù)不能有null值

添加約束：

CREATE TABLE 表名(
	列名 數(shù)據(jù)類型 NOT NULL,
	…
);

建完表后添加非空約束：

ALTER TABLE 表名 MODIFY 字段名 數(shù)據(jù)類型 NOT NULL;

刪除約束：

ALTER TABLE 表名 MODIFY 字段名 數(shù)據(jù)類型;

4. 唯一約束

目的：保證列中所有數(shù)據(jù)各不相同

添加約束：

CREATE TABLE 表名(
	列名 數(shù)據(jù)類型 UNIQUE [AUTO_INCREMENT],
	-- AUTO_INCREMENT: 當(dāng)不指定值時(shí)自動(dòng)增長(zhǎng)
	…
);
CREATE TABLE 表名(
	列名 數(shù)據(jù)類型,
	…
	[CONSTRAINT] [約束名稱] UNIQUE(列名)
);

建完表后添加唯有約束：

ALTER TABLE 表名 MODIFY 字段名 數(shù)據(jù)類型 UNIQUE;

刪除約束：

ALTER TABLE 表名 DROP INDEX 字段名;

5. 主鍵約束

目的：主鍵是一行數(shù)據(jù)的唯一標(biāo)識(shí)，要求非空且唯一

添加約束：

CREATE TABLE 表名(
	列名 數(shù)據(jù)類型 PRIMARY KEY [AUTO_INCREMENT],
	…
);
CREATE TABLE 表名(
	列名 數(shù)據(jù)類型,
	[CONSTRAINT] [約束名稱] PRIMARY KEY(列名)
);

建完表后添加主鍵約束：

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

刪除約束：

ALTER TABLE 表名 DROP PRIMARY KEY;

6. 默認(rèn)約束

目的：保存數(shù)據(jù)時(shí)，未指定值則采用默認(rèn)值

添加約束：

CREATE TABLE 表名(
	列名 數(shù)據(jù)類型 DEFAULT 默認(rèn)值,
	…
);

建完表后添加默認(rèn)約束：

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默認(rèn)值;

刪除約束：

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

7. 外鍵約束

當(dāng)我們添加了外鍵以后，就在數(shù)據(jù)庫(kù)層面建立了兩張表的關(guān)系。

目的：外鍵用來(lái)讓兩個(gè)表的數(shù)據(jù)之間建立鏈接，保證數(shù)據(jù)的一致性和完整性

添加約束：

CREATE TABLE 表名(
	列名 數(shù)據(jù)類型,
	…
	[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵列名) REFERENCES 主表(主表列名)
);

建完表后添加外鍵約束：

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);

刪除約束：

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

8. 約束的案例練習(xí)

首先，我們使用以下的案例來(lái)練習(xí)約束：

-- 刪除stu表
drop table if exists stu;
-- 創(chuàng)建stu表
CREATE TABLE stu (
id int primary key, -- 編號(hào) 主鍵
name varchar(10)  not null unique, -- 姓名 非空，唯一
age int not null, -- 年齡 非空
gender varchar(5) not null, -- 性別 非空
math double(5,2) not null, -- 數(shù)學(xué)成績(jī)  非空
english double(5,2) default 0 -- 英語(yǔ)成績(jī) 默認(rèn)為0
);

驗(yàn)證主鍵約束，其特點(diǎn)是非空且唯一，先添加一條數(shù)據(jù)：

insert into stu(id,name,age,gender,math,english) values(1,'小張',23,'男',66,78);

添加第二條數(shù)據(jù)時(shí)，嘗試 id 添加為空值：

insert into stu(id,name,age,gender,math,english) values (null,'小李',20,'女',98,87);

嘗試 id 添加為重復(fù)的值：

insert into stu(id,name,age,gender,math,english) values (1,'小陳',55,'男',56,77);

我們已經(jīng)驗(yàn)證了主鍵約束，當(dāng)我們添加不合法的數(shù)據(jù)時(shí)，添加失敗。

驗(yàn)證非空約束：

當(dāng)我們添加以下的數(shù)據(jù)時(shí)：

insert into stu(id,name,age,gender,math,english) values (2,NULL,20,'女',76,65);

驗(yàn)證唯一約束：

當(dāng)我們添加以下的數(shù)據(jù)時(shí)：

insert into stu(id,name,age,gender,math,english) values 
(5,'小張',20,'男',86,NULL);

驗(yàn)證默認(rèn)約束：

當(dāng)我們添加以下的數(shù)據(jù)時(shí)：

insert into stu(id,name,age,gender,math) values (6,'小趙',23,'男',99);

驗(yàn)證外鍵約束：

我們使用一下的案例來(lái)驗(yàn)證外鍵約束：

-- 刪除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部門表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 員工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外鍵 dep_id,關(guān)聯(lián) dept 表的id主鍵
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

此時(shí)，我們?cè)趩T工表中添加了外鍵，相當(dāng)于在數(shù)據(jù)庫(kù)層面上建立了兩張表的關(guān)系，此時(shí)如果員工表中有員工 a ，他屬于 1 號(hào)部門，那么我們想要?jiǎng)h除部門表中的 1 號(hào)部門就會(huì)刪除失敗，因?yàn)閱T工 a 是屬于 1 號(hào)部門的，此時(shí)兩張表建立了關(guān)系。

添加數(shù)據(jù)：

-- 添加 2 個(gè)部門
insert into dept(dep_name,addr) values
('研發(fā)部','西安'),('銷售部', '成都');
-- 添加員工,dep_id 表示員工所在的部門
INSERT INTO emp (NAME, age, dep_id) VALUES
('張三', 20, 1),
('李四', 20, 2);

此時(shí)想要是刪除銷售部門時(shí)，發(fā)現(xiàn)刪除失敗。