快捷導(dǎo)航

一文帶你了解MySQL之約束

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

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

1. 約束的概念

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

2. 約束的分類

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

約束類型	功能
非空約束 NOT NULL	保證列中所有的數(shù)據(jù)不能有null值
唯一約束 UNIQUE	保證列中所有數(shù)據(jù)各不相同
主鍵約束 PRIMARY KEY	主鍵是一行數(shù)據(jù)的唯一標(biāo)識，要求非空且唯一
檢查約束 CHECK	保證列中的值滿足某一條件
默認(rèn)約束 DEFAULT	保存數(shù)據(jù)時，未指定值則采用默認(rèn)值
外鍵約束 FOREIGN KEY	外鍵用來讓兩個表的數(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)不指定值時自動增長
	…
);
CREATE TABLE 表名(
	列名 數(shù)據(jù)類型,
	…
	[CONSTRAINT] [約束名稱] UNIQUE(列名)
);

建完表后添加唯有約束：

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

刪除約束：

ALTER TABLE 表名 DROP INDEX 字段名;

5. 主鍵約束

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

添加約束：

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ù)時，未指定值則采用默認(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ù)庫層面建立了兩張表的關(guān)系。

目的：外鍵用來讓兩個表的數(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í)

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

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

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

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

添加第二條數(shù)據(jù)時，嘗試 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)驗證了主鍵約束，當(dāng)我們添加不合法的數(shù)據(jù)時，添加失敗。

驗證非空約束：

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

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

驗證唯一約束：

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

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

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

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

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

驗證外鍵約束：

我們使用一下的案例來驗證外鍵約束：

-- 刪除表
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)
);

此時，我們在員工表中添加了外鍵，相當(dāng)于在數(shù)據(jù)庫層面上建立了兩張表的關(guān)系，此時如果員工表中有員工 a ，他屬于 1 號部門，那么我們想要刪除部門表中的 1 號部門就會刪除失敗，因為員工 a 是屬于 1 號部門的，此時兩張表建立了關(guān)系。

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

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

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