前言：

• 學習和使用數據庫可以說是程序員必須具備能力，這里將更新關于MYSQL的使用講解，大概應該會更新30篇+，涵蓋入門、進階、高級(一些原理分析);
• 這一篇講解“約束”，如：非空、唯一、主鍵、外鍵、級聯、默認、檢查約束等;
• 雖然MYSQL命令很多，但是自己去多敲一點，到后面忘記了，查一下就可以回憶起來使用了；
• 這一系列也是本人學習MYSQL做的筆記，也是為了方便后面忘記查詢；
• 參考資料：尚硅谷、黑馬、csdn和知乎博客；
• 歡迎收藏 + 關注，本人將會持續更新后端和AI算法的學習筆記。
  

  文章目錄

  • 約束
  • • 1. 約束(Constraint)概念
    • • 1.1 為什么需要約束
      • 1.2 什么是約束
      • 1.3 約束分類
      • • 1.3.1 根據約束的字段分類
        • 1.3.2 根據約束的作用范圍分類
        • 1.3.3 根據約束的作用分類
    • 2. 非空約束(NK)
    • • 2.1 概念
      • **2.2 特點**
      • 2.3 添加非空約束
      • 2.4 刪除非空約束
    • 3. 唯一約束(UK)
    • • 3.1 概念
      • 3.2 特點
      • 3.3 添加唯一約束
      • 3.4 刪除唯一約束
    • 4. 主鍵約束(PK)
    • • 4.1 概念
      • 4.2 特點
      • 4.3 單字段主鍵
      • 4.4 多字段主鍵(復合主鍵)
      • 4.5 刪除主鍵
      • 4.5 設置字段值自動增加(AUTO_INCREMENT)
      • • 4.5.1 概念
        • 4.5.2 特點
        • 4.5.3 指定自增約束
        • 4.5.3 刪除自增約束(和非空一樣)
    • 5. 外鍵約束(FOREIGN KEY,FK)
    • • 5.1 概念
      • 5.2 特點
      • 5.3 添加外鍵約束
      • 5.4 演示
      • • 5.4.1 創建表
        • 5.4.2 操作表
      • 5.5 阿里開發規范
      • 5.6 級聯操作
    • 6. 默認值約束和檢查約束
    • • 6.1 檢查約束（Check）
      • 6.2 默認值約束(Default)

約束

1. 約束(Constraint)概念

1.1 為什么需要約束

數據完整性（Data Intergrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。

對于已經創建好的表，雖然字段的數據類型決定了所能存儲的數據類型，但是表中所存儲的數據是否合法并沒有進行檢查，比如：性別只能填男女，或者其他(0, 1這一類數字)，這個時候會就需要對輸入的數據進行檢查了。

在具體使用MySQL 軟件時，如果想針對表中的數據做一些完整性檢查操作，可以通過表的約束來完成。

為了保證數據的完整性，SQL規范以約束的方式對表數據進行額外的條件限制，主要從以下四個方面考慮：

• 實體完整性（Entity Integrity）如：同一張表中，不能存在兩條完全相同無法區分的記錄
• 域完整性（Domain Integrity）如：年齡范圍0-120，性別范圍 “男/女”
• 引用完整性（Referential Integrity）如：員工所在部門，必須要在部門表中存在
• 用戶自定義完整性（User-defined Integrity）如：用戶名唯一、密碼不能為空等

1.2 什么是約束

約束是表級的強制規定。

可以在創建表時規定約束（通過CREATE TABLE語句），或者在表創建之后通過ALTER T ABLE語句規定約束。

1.3 約束分類

1.3.1 根據約束的字段分類

• 單列約束
• 多列約束

1.3.2 根據約束的作用范圍分類

• 列級約束
• 表級約束

區別

① 位置不同 ：列級約束是寫在列的后面，標記約束時寫在所有字段的最后面

② 支持的約束類型不同：列級約束可以支持所有的約束類型，表級約束不能支持非空和默認

③ 列級約束不可以起約束名，表記約束可以起約束名（主鍵除外，主鍵使用的PRIMARY KEY）

1.3.3 根據約束的作用分類

完整性約束關鍵字	說明
NOT NULL(NK)	約束字段的值不能為空
DEFAULT	設置字段的默認值
UNIQUE KEY(UK)	約束字段的值是唯一(同一列不能出現相同的值)
PRIMARAY KEY(PK)	約束字段為表的主鍵，可以作為該表記錄的唯一標識
AUTO_INCREMENT	約束字段的值為自動增加
FOREIGN KEY(FK)	約束字段為表的外鍵
CHECK()

• 查看某個表已有的約束

  SELECT  * FROM information_schema.TABLE_CONSTRAINTS
  WHERE table_name='emp';
  

2. 非空約束(NK)

2.1 概念

當數據庫表中的某個字段上的內容不希望設置為NULL時，則可以使用NK約束進行設置。

使用： 在創建數據庫表時為某些字段加上“NOT NULL”約束條件，保證所有記錄中該字段都有值。如果用戶插入的記錄中，該字段為空值，則數據庫管理系統會報錯。

2.2 特點

• 默認情況下，所有類型的值都可以是NULL
• 一個表可以有很多字段指定非空約束
• 空串" "不等于NULL，0也不等于NULL，NULL也不等于NULL

2.3 添加非空約束

1. 創建表時

CREATE TABLE table_name(字段名 數據類型 NOT NULL,...
);

2. 創建之后添加

ALTER TABLE <表名> MODIFY 字段名 數據類型 約束;

2.4 刪除非空約束

# modify 本質是修改字段屬性
ALTER TABLE 表名 MODIFY 字段名 數據類型 NULL;
#或
ALTER TABLE 表名 MODIFY 字段名 數據類型;

3. 唯一約束(UK)

3.1 概念

當數據庫表中的某個字段上的內容不允許重復時，則可以使用UK約束進行設置。

使用：UK約束在創建數據庫表時為某些字段加上“UNIQUE”約束條件，保證所有記錄中該字段上的值不重復。

如果用戶插入的記錄中，該字段上的值與其他記錄里該字段上的值重復，則數據庫管理系統會報錯。

3.2 特點

• 同一個表可以有多個唯一約束
• 唯一約束允許值為NULL
• 在創建唯一約束的時候，如果不給唯一約束命名，那么約束名就和字段名相同

3.3 添加唯一約束

1. 創建表時添加

CREATE TABLE table_name(字段名 數據類型 UNIQUE KEY,...
);

2. 創建表后添加

• 方法一

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

• 方法二

ALTER TABLE <表名> ADD [CONSTRAINT 約束名] UNIQUE [KEY](字段名)；

3.4 刪除唯一約束

唯一約束特點：

• 添加唯一約束的列上會自動創建唯一索引
• 刪除唯一約束只能通過刪除唯一索引的方式刪除
• 刪除時需要指定唯一索引名，唯一收益名和唯一約束名相同。
• 如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；如果是組合列，那么默認和（）中第一個列名相同。

ALTER TABLE <表名> DROP INDEX <索引名>;#查看索引名
SELECT  * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name='emp';

? 可以通過SHOW INDEX FROM 表名;查看表的所有

4. 主鍵約束(PK)

4.1 概念

當想用數據庫表中的某個字段來唯一標識所有記錄時，則可以使用PK約束進行設置。

使用：PK約束在創建數據庫表時為某些字段加上“PRIMARY KEY”約束條件，則該字段可以唯一地標示所有記錄。

作用：在數據庫表中之所以設置主鍵，是為了便于數據庫管理系統快速地查找到表中的記錄。

注意： 在具體設置主鍵約束時，必須要滿足主鍵字段的值是唯一、非空的。

4.2 特點

• 主鍵約束相當于 唯一約束+非空約束，意味著不允許重復和NULL值
• 主鍵約束對應著表中的一列或者多列(復合主鍵)
• 如果是多列組合的復合主鍵約束，那么這些列都不允許為NULL值，并且組合的值不允許重復
• 當創建主鍵約束時，系統默認會在所在的列或組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高），如果刪除主鍵約束，主鍵約束對應的索引就自動刪除了
• 需要注意一點的是，不要修改主鍵字段的值。因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性。

4.3 單字段主鍵

• 創建表時指定主鍵約束

CREATE TABLE table_name(字段名 數據類型  PRIMARY KEY,...
);

• ALTER TABLE添加主鍵約束，注意：如果在添加之前字段里存在空或者不唯一情況，則添加失敗

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

4.4 多字段主鍵(復合主鍵)

當主鍵有多個字段組合而成時，需要通過SQL語句CONSTRAINT來實現，語法形式如下：

CREATE TABLE table_name(字段名 數據類型，...[CONSTRAINT 約束名] PRIMARY KEY(字段1，字段2...)
);

在上述語句中，在字段定義完之后統一設置主鍵，PRIMARY KEY關鍵字括號中的字段可以有多個，需要通過逗號分割，用來實現設置多字段主鍵。

4.5 刪除主鍵

ALTER TABLE <表名> DROP PRIMARY KEY;

4.5 設置字段值自動增加(AUTO_INCREMENT)

4.5.1 概念

AUTO_INCREMENT是 MySQL 唯一擴展的完整性約束，當為數據庫表中插入新記錄時，字段上的值會自動生成唯一的ID。

由于設置AUTO_INCREMENT約束后的字段會生成唯一的ID，所以該字段也經常會設置成PK主鍵。

4.5.2 特點

• 一個表最多只能有一個自增長列
• 當需要產生唯一標識符或者順序值時，可設置自增長
• 自增長列約束的列必須是鍵列(主鍵列，唯一鍵列)
• 自增約束的列的數據類型必須是整數類型
• 如果自增列為NULL，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值具體值。

4.5.3 指定自增約束

1. 方法一：創建表是指定

CREATE TABLE table_name(字段名 數據類型 PRIMARY KEY|UNIQUE AUTO_INCREMENT,...
);

2. 方法二：通過ALTER TABLE添加

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

4.5.3 刪除自增約束(和非空一樣)

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

5. 外鍵約束(FOREIGN KEY,FK)

前面介紹的完整性約束都是在單表中進行設置，而外鍵約束則保證多個表(通常為兩個表）之間的參照完整性，即構建于兩個表的兩個字段之間的參照關系。

5.1 概念

設置外鍵約束的兩個表之間會具有父子關系，即子表中某個字段的取值范圍由父表所決定。例如，表示一種部門和雇員關系，即每個部門有多個雇員。首先應該有兩個表：部門表和雇員表，然后雇員表中有一個表示部門編號的字段deptno，其依賴于部門表的主鍵，這樣字段deptno就是雇員表的外鍵，通過該字段部門表和雇員表建立了關系。

對于兩個具有關聯關系的表而言，相關聯字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）。

在具體設置FK約束時，設置FK約束的字段必須依賴于數據庫中已經存在的父表的主鍵，同時外鍵可以為NULL。

5.2 特點

• 從表的外鍵列必須引用(參考)主表的主鍵或唯一約束的列：因為參考的值必須是唯一的
• 在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名，如emp11_ibfk_1，也可以指定外鍵約束名
• 創建表時必須先創建主表，再創建從表
• 刪除表是必須先刪除從表(或先刪除外鍵約束)，再刪除主表
• 當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該字段的記錄，然后才可以刪除主表中的記錄
• 當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引；刪除外鍵約束之后，必須手動刪除對應的索引

看起來，很麻煩，😂，但是一般不用，阿里規范中禁止使用，很明顯，從表依賴于主表，尤其是數據變化的時候，還涉及到級聯操作，如果數據量龐大同時進行修改，這個無疑是一個非常耗時的操作。

5.3 添加外鍵約束

設置表中某字段的FK 約束非常簡單，其語法形式如下:

CREATE TABLE table_name(字段名 數據類型，字段名 數據類型，...CONSTRAINT 外鍵約束名 FOREIGN KEY (字段名1)REFERENCES 主表名 (字段名2)
);

1、建表時添加

# 主表，創建主鍵
CREATE TABLE 主表名
(字段名 數據類型 PRIMARY KEY,字段名 數據類型，...
);# 從表，創建從鍵
CREATE TABLE 從表名
(字段名 數據類型 PRIMARY KEY,字段名 數據類型，...CONSTRAINT 約束名 FOREIGN KEY(外鍵約束字段名)REFERENCES 主表(參考字段名)
);

2、建表后添加

一般情況下，表與表的關聯都是提前設計好的，因此，會在創建表的時候就把外鍵約束定義好，但是也有例外，表后添加語法如下：

ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY(從表字段名) REFERENCES 主表名(被參考字段) [ON UPDATE XX][ON DELETE XX];

范例：

ALTER TABLE emp ADD [CONSTRAINT fk_emp_deptno] FOREIGN KEY(deptno) REFERENCES dept(deptno);

5.4 演示

5.4.1 創建表

#新建數據庫
CREATE DATABASE db_maye;
USE db_maye;#創建表
CREATE TABLE dept
(deptno INT PRIMARY KEY COMMENT '部門編號',dname VARCHAR(20) NOT NULL  COMMENT '部門名稱',loc   VARCHAR(20)  COMMENT '部門所在位置'
);# 創建從表
CREATE TABLE emp
(empno INT PRIMARY KEY  COMMENT '員工編號',ename VARCHAR(10) NOT NULL  COMMENT '員工姓名',deptno INT  COMMENT '員工所在部門編號',	#外鍵必須使用表級約束CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

5.4.2 操作表

• 添加記錄

#給員工表添加一條記錄
INSERT INTO emp(empno,ename,deptno) VALUES(1234,'wy',10);
# 注意：由于主表中沒有 10 號部門，故會報錯。#先給dept添加記錄，再執行上面的插入語句即可
INSERT INTO dept(deptno,dname,loc) VALUES(10,'hit','深圳');  # 這個時候從表可以添加部門為10號

• 刪除記錄

#刪除主表dept中的記錄，但是會報錯，** 必須先刪除從表中deptno=10的信息 **
DELETE FROM dept WHERE deptno=10;#先刪除從表emp中參考指定值的記錄,再執行上面的刪除語句，這樣即可刪除
DELETE FROM emp WHERE deptno=10;

• 修改記錄

#修改主表dept中的部門編號(把部門編號為10的改為20)，這個也會報錯，因為修改了后，從表數據沒有得到修改，所以，可以先刪除從表中deptno=10的部門，在修改主表
UPDATE dept SET deptno=20 WHERE deptno=10;#先刪除從表emp中參考指定值的記錄，再執行上面的語句，即可成功
DELETE FROM emp WHERE deptno=10;

• 刪除約束和索引

#先查看約束名
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name='emp1';#刪除外鍵約束
ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_deptno;#查看指定表的索引
SHOW INDEX FROM emp1;
#最后手動刪除索引
ALTER TABLE emp1 DROP INDEX fk_emp1_deptno;

5.5 阿里開發規范

【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。

說明：部門表dept中的deptno是主鍵，那么員工表emp中的empno則為外鍵。如果更新部門表中的deptno，同時觸發員工表中的deptno更新，即為級聯更新。外鍵與級聯更新適用于單機低并發，不適合分布式、高并發集群；級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。

5.6 級聯操作

從上面外鍵演示中可以看出，數據的增刪改查的時候，主表和從表很多時候，需要有先后關系，比如：

• 當我們需要刪除部門表(主表)信息時，必須先刪除員工表(從表)中關聯的數據。

級聯操作就是解決這個問題的：

• 級聯操作指的就是，當你操作主表時，自動的操作從表，當然這個操作分為幾種情況，下面會講。

一般對于從表和主表最常用的是：兩種級聯操作

• 級聯刪除：當刪除主表數據是時自動刪除從表中相關數據
• 級聯更新：當一般是主鍵更新時，自動更新從表的數據

五種級聯方式

定義從表的外鍵時指定的ON UPDATE/ON DELETE子句, InnoDB支持５種方式，分列如下 ：

• CASCADE 級聯方式，這個適用場景比較多。
  • 在主表上update/delete記錄時，同步update/delete掉從表的匹配記錄
• SET NULL 設置為NULL
  • 在主表上update/delete記錄時，將從表上匹配記錄的列為NULL
• NO ACTION 不允許更新和刪除
  • 如果從表中有匹配的記錄，則不允許對主表的關聯字段更新
• RESTRICT 限制
  • 同NO ACTION
• SET DEFAULT
  • 主表有變更時，子表將外鍵列設置成一個默認的值，但Innodb不能識別…

注意：可以支持多種方式

范例：

CREATE TABLE emp
(empno INT PRIMARY KEY  COMMENT '員工編號',ename VARCHAR(10) NOT NULL  COMMENT '員工姓名',deptno INT  COMMENT '員工所在部門編號',	#外鍵必須使用表級約束CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)ON UPDATE CASCADE ON DELETE RESTRICT  # 作用：主表和從表同步更新，刪除的時候從表有匹配的，則主表不允許刪除
);

6. 默認值約束和檢查約束

6.1 檢查約束（Check）

作用：檢查某個字段的值是否符合什么要求，一般指的是值的范圍。

• 創建表時添加檢查約束

#方法一
CREATE TABLE stu
(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10),gender CHAR CHECK(gender IN('男','女'))   # 簡單
);#2
CREATE TABLE stu
(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10),gender CHAR CHECK(gender IN('男','女')),age TINYINT,sal DECIMAL(10,2),CONSTRAINT ck_stu_sal CHECK(sal>200),  # CONSTRAINT 約束的意思CONSTRAINT ck_stu_age CHECK(age BETWEEN 18 AND 120)
);

• 創建表后添加檢查約束

ALTER TABLE stu ADD CONSTRAINT ck_stu_sal CHECK(sal>2000);

刪除檢查約束

ALTER TABLE stu DROP CHECK ck_stu_sal;

6.2 默認值約束(Default)

當為數據庫表中插入一條新記錄時，如果沒有為某個字段賦值，可以設置默認值。

語法形式如下:

• 創建表時添加

CREATE TABLE df
(id INT PRIMARY KEY,name VARCHAR(10) DEFAULT 'maye'
);

• 創建表后添加

ALTER TABLE df MODIFY name VARCHAR(10) DEFAULT 'hello';

• 刪除默認約束

ALTER TABLE df MODIFY name VARCHAR(10);