腳本之家服務器常用軟件

快捷導航

一文帶你了解MySQL之約束

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

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

1. 約束的概念

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

2. 約束的分類

前面說到SQL標準把約束分為了6大類，分別是非空約束，唯一約束，主鍵約束，檢查約束，默認約束和外鍵約束，添加約束時我們只需要在SQL中添加關鍵詞，便可以限制表中的數據。

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

3. 非空約束

目的：保證列中所有的數據不能有null值

添加約束：

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

建完表后添加非空約束：

ALTER TABLE 表名 MODIFY 字段名 數據類型 NOT NULL;

刪除約束：

ALTER TABLE 表名 MODIFY 字段名 數據類型;

4. 唯一約束

目的：保證列中所有數據各不相同

添加約束：

CREATE TABLE 表名(
	列名 數據類型 UNIQUE [AUTO_INCREMENT],
	-- AUTO_INCREMENT: 當不指定值時自動增長
	…
);
CREATE TABLE 表名(
	列名 數據類型,
	…
	[CONSTRAINT] [約束名稱] UNIQUE(列名)
);

建完表后添加唯有約束：

ALTER TABLE 表名 MODIFY 字段名 數據類型 UNIQUE;

刪除約束：

ALTER TABLE 表名 DROP INDEX 字段名;

5. 主鍵約束

目的：主鍵是一行數據的唯一標識，要求非空且唯一

添加約束：

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

建完表后添加主鍵約束：

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

刪除約束：

ALTER TABLE 表名 DROP PRIMARY KEY;

6. 默認約束

目的：保存數據時，未指定值則采用默認值

添加約束：

CREATE TABLE 表名(
	列名 數據類型 DEFAULT 默認值,
	…
);

建完表后添加默認約束：

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默認值;

刪除約束：

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

7. 外鍵約束

當我們添加了外鍵以后，就在數據庫層面建立了兩張表的關系。

目的：外鍵用來讓兩個表的數據之間建立鏈接，保證數據的一致性和完整性

添加約束：

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

建完表后添加外鍵約束：

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

刪除約束：

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

8. 約束的案例練習

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

-- 刪除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, -- 數學成績  非空
english double(5,2) default 0 -- 英語成績 默認為0
);

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

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

添加第二條數據時，嘗試 id 添加為空值：

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

嘗試 id 添加為重復的值：

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

我們已經驗證了主鍵約束，當我們添加不合法的數據時，添加失敗。

驗證非空約束：

當我們添加以下的數據時：

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

驗證唯一約束：

當我們添加以下的數據時：

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

驗證默認約束：

當我們添加以下的數據時：

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,關聯(lián) dept 表的id主鍵
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

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

添加數據：

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

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