MySQL數(shù)據(jù)庫表中的約束詳解

更新時間：2023年03月02日 09:03:50 作者：瀛臺夜雪

約束是用來限制表中的數(shù)據(jù)長什么樣子的,即什么樣的數(shù)據(jù)可以插入到表中,什么樣的數(shù)據(jù)插入不到表中,下面這篇文章主要給大家介紹了關(guān)于如何通過一文理解MySQL數(shù)據(jù)庫的約束與表的設(shè)計的相關(guān)資料,需要的朋友可以參考下

MySQL表中的約束(constraint)

為了保證數(shù)據(jù)的完整性，（數(shù)據(jù)的精確性和可靠性）

SQL規(guī)范以約束的方式對表數(shù)據(jù)進行額外的條件限制，可從以下四個方面進行考慮

實體完整性
域完整性
引用完整性
用戶自定義完整性

約束？對表中字段的限制。

約束分類

約束作用字段的個數(shù)

單列約束
多列約束

根據(jù)約束的作用范圍

列級約束，將此約束聲明放在對應(yīng)字段的后面
表級約束：在表中所有字段聲明完，在所有字段的后面聲明約束

根據(jù)約束起的作用

NOT NULL 非空約束，規(guī)定某個字段不能為空
UNIQUE 唯一約束，規(guī)定某個字段在整個表中是唯一的
PRIMARY KEY 主鍵(非空且唯一)約束
FOREIGN KEY 外鍵約束
CHECK 檢查約束
DEFAULT 默認值約束

如何添加約束/刪除約束

CREATE TABLE時添加約束
ALTER TABLE時增加約束、刪除約束

如何查看表中的約束

SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME='employees';

非空約束

限制某個字段/某列的值不為空

關(guān)鍵字： NOT NULL

默認，所有的類型的值都可以是NULL，包括INT、FLOAT等數(shù)據(jù)類型
非空約束只能出現(xiàn)在表對象的列上，只能某個列單獨限定非空，不能組合非空
一個表可以有很多列都分別限定了非空
空字符串’'不等于NULL，0也不等于NULL

添加非空約束

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
salary DECIMAL(10,2)
)

ALTER TABLE test1
MODIFY salary DECIMAL(10,2) NOT NULL;

刪除非空約束

ALTER TABLE test1
MODIFY salary DECIMAL(10,2);

唯一性約束

用來限制某個字段/某列的值不能重復(fù)

關(guān)鍵字：UNIQUE

同一個表可以有多個唯一約束。
唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
唯一性約束允許列值為空。
在創(chuàng)建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同。
MySQL會給唯一約束的列上默認創(chuàng)建一個唯一索引。

添加唯一性約束

CREATE TABLE test2(
id INT UNIQUE, #列約束
last_name VARCHAR(15) ,
email VARCHAR(25) ,
salary DECIMAL(10,2),
#表約束
CONSTRAINT uk_test2_email UNIQUE(email)
)

可以項聲明未unique的字段上添加null值，而且可以多次添加NULL值

方式一：

ALTER TABLE test2
ADD CONSTRAINT uk_test2_salary UNIQUE(salary);

方式二:

ALTER TABLE test2
MODIFY last_name VARCHAR(20) UNIQUE;

復(fù)合約束

CREATE TABLE `USER`(
id INT,
name VARCHAR(15),
password varchar(25),
#表約束實現(xiàn)多行約束
CONSTRAINT uk_user_name_pwd UNIQUE(name,password)
);

create table 表名稱(
字段名數(shù)據(jù)類型,
字段名數(shù)據(jù)類型,
字段名數(shù)據(jù)類型,
unique key(字段列表) #字段列表中寫的是多個字段名，多個字段名用逗號分隔，表示那么是復(fù)合唯一，即多
個字段的組合是唯一的
);

刪除唯一約束

添加唯一性約束的列上也會自動創(chuàng)建唯一索引。
刪除唯一約束只能通過刪除唯一索引的方式刪除。
刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
如果創(chuàng)建唯一約束時未指定名稱，如果是單列，就默認和列名相同；
如果是組合列，那么默認和() 中排在第一個的列名相同。也可以自定義唯一性約束

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

主鍵約束

用來唯一標(biāo)識表中的一行記錄

關(guān)鍵字：primary key

主鍵約束相當(dāng)于唯一約束+非空約束的組合，主鍵約束列不允許重復(fù)，也不允許出現(xiàn)空值

一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別創(chuàng)建，也可以在表級別上創(chuàng)建。
主鍵約束對應(yīng)著表中的一列或者多列（復(fù)合主鍵）
如果是多列組合的復(fù)合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復(fù)。
MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用。
當(dāng)創(chuàng)建主鍵約束時，系統(tǒng)默認會在所在的列或列組合上建立對應(yīng)的主鍵索引（能夠根據(jù)主鍵查詢的，就根據(jù)主鍵查詢，效率更高。如果刪除主鍵約束了，主鍵約束對應(yīng)的索引就自動刪除了。
需要注意的一點是，不要修改主鍵字段的值。因為主鍵是數(shù)據(jù)記錄的唯一標(biāo)識，如果修改了主鍵的值，就有可能會破壞數(shù)據(jù)的完整性。

添加主鍵約束

CREATE TABLE test3(
id INT PRIMARY KEY, #列級約束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test4(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表約束，沒有必要取別名
CONSTRAINT pk_test5_id PRIMARY KEY(id)
);

CREATE TABLE test6(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
DESC test6;
ALTER TABLE test6
ADD PRIMARY KEY(id)

復(fù)合主鍵約束

CREATE TABLE test5(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表約束
PRIMARY KEY(id,last_name)
);

刪除主鍵約束

在實際開發(fā)中根本不會這樣做

ALTER TABLE test6
DROP PRIMARY KEY;

自增列-AUTO_INCREMENT

某個字段的值自增

關(guān)鍵字：auto_increment

（1）一個表最多只能有一個自增長列

（2）當(dāng)需要產(chǎn)生唯一標(biāo)識符或順序值時，可設(shè)置自增長

（3）自增長列約束的列必須是鍵列（主鍵列，唯一鍵列）

（4）自增約束的列的數(shù)據(jù)類型必須是整數(shù)類型

（5）如果自增列指定了 0 和 null，會在當(dāng)前最大值的基礎(chǔ)上自增；如果自增列手動指定了具體值，直接賦值為具體值。

當(dāng)向主鍵（含AUTO_INCREMENT）的字符上添加0或null時，實際上會自動的往上添加指定字段的數(shù)值

添加自增約束

開發(fā)中，一旦主鍵作用的字段聲明有AUTO_INCREMENT,則我們在添加數(shù)據(jù)時，就不要給主鍵賦值了

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT, 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

ALTER TABLE test7
MODIFY id INT AUTO_INCREMENT;

刪除自增約束

ALTER TABLE test7
MODIFY id INT

外鍵約束FOREIGN KEY約束

限定某個表的某個字段的引用完整性

關(guān)鍵字：FOREIGN KEY

主表（父表）：被引用的表，被參考的表

從表（子表）：引用別人的表，參考別人的表

（1）從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列為什么？因為被依賴/被參考的值必須是唯一的

（2）在創(chuàng)建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產(chǎn)生一個外鍵名（例如 student_ibfk_1;），也可以指定外鍵約束名。

（3）創(chuàng)建(CREATE)表時就指定外鍵約束的話，先創(chuàng)建主表，再創(chuàng)建從表

（4）刪表時，先刪從表（或先刪除外鍵約束），再刪除主表

（5）當(dāng)主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數(shù)據(jù)，需要先刪除從表中依賴該記錄的數(shù)據(jù)，然后才可以刪除主表的數(shù)據(jù)

（6）在“從表”中指定外鍵約束，并且一個表可以建立多個外鍵約束

（7）從表的外鍵列與主表被參照的列名字可以不相同，但是數(shù)據(jù)類型必須一樣，邏輯意義一致。如果類型不一樣，創(chuàng)建子表時，就會出現(xiàn)錯誤“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。例如：都是表示部門編號，都是int類型。

（8）當(dāng)創(chuàng)建外鍵約束時，系統(tǒng)默認會在所在的列上建立對應(yīng)的普通索引。但是索引名是外鍵的約束名。（根據(jù)外鍵查詢效率很高）

（9）刪除外鍵約束后，必須手動刪除對應(yīng)的索引

添加外鍵約束

創(chuàng)建主表

CREATE TABLE dept1(
dept_id INT PRIMARY KEY,
dept_num VARCHAR(15)
)

創(chuàng)建從表

CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_num VARCHAR(15),
department_id  INT,
CONSTRAINT fk_emp2_dept1_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
)

建表以后添加外鍵約束

ALTER TABLE emp2
ADD fk_emp2_dept1_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)

約束等級

Cascade方式：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
Set null方式：在父表上update/delete記錄時，將子表上匹配記錄的列設(shè)為null，但是要注意子表的外鍵列不能為not null
No action方式：如果子表中有匹配的記錄，則不允許對父表對應(yīng)候選鍵進行update/delete操作
Restrict方式：同no action，都是立即檢查外鍵約束
Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時，子表將外鍵列設(shè)置成一個默認的值，但Innodb不能識別x

如果沒有指定等級，就相當(dāng)于Restrict方式。對于外鍵約束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

刪除外鍵約束

ALTER TABLE emp2
DROP FOREIGN KEY fk_emp2_dept1_id;

刪除外鍵約束對應(yīng)索引

SHOW INDEX FROM emp2;
ALTER TABLE emp2
DROP INDEX fk_emp2_dept1_id;

開發(fā)場景

問題1：如果兩個表之間有關(guān)系（一對一、一對多），比如：員工表和部門表（一對多），它們之間是否一定要建外鍵約束？

答：不是的

問題2：建和不建外鍵約束有什么區(qū)別？

答：建外鍵約束，你的操作（創(chuàng)建表、刪除表、添加、修改、刪除）會受到限制，從語法層面受到限制。例如：在員工表中不可能添加一個員工信息，它的部門的值在部門表中找不到。

不建外鍵約束，你的操作（創(chuàng)建表、刪除表、添加、修改、刪除）不受限制，要保證數(shù)據(jù)的引用完整性，只能依靠程序員的自覺，或者是在Java程序中進行限定。例如：在員工表中，可以添加一個員工的信息，它的部門指定為一個完全不存在的部門。

問題3：那么建和不建外鍵約束和查詢有沒有關(guān)系？

答：沒有

在 MySQL 里，外鍵約束是有成本的，需要消耗系統(tǒng)資源。對于大并發(fā)的 SQL 操作，有可能會不適合。比如大型網(wǎng)站的中央數(shù)據(jù)庫，可能會因為外鍵約束的系統(tǒng)開銷而變得非常慢。所以， MySQL 允許你不使用系統(tǒng)自帶的外鍵約束，在應(yīng)用層面完成檢查數(shù)據(jù)一致性的邏輯。也就是說，即使你不用外鍵約束，也要想辦法通過應(yīng)用層面的附加邏輯，來實現(xiàn)外鍵約束的功能，確保數(shù)據(jù)的一致性。

阿里開發(fā)規(guī)范

【強制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

說明：（概念解釋）學(xué)生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學(xué) 生表中的 student_id，同時觸發(fā)成績表中的 student_id 更新，即為級聯(lián)更新。外鍵與級聯(lián)更新適用于單機低并發(fā) ，不適合分布式、高并發(fā)集群；級聯(lián)更新是強阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險；外鍵影響數(shù)據(jù)庫的插入速度。

CHECK約束

檢查某個字段的值是否符合xxx要求，一般指值的范圍

MySQL5.7 可以使用check約束，但check約束對數(shù)據(jù)驗證沒有任何作用。添加數(shù)據(jù)時，沒有任何錯誤或警告

但是MySQL 8.0中可以使用check約束了。

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);

DEFAULT約束

給某個字段/某列指定默認值，一旦設(shè)置默認值，在插入數(shù)據(jù)時，如果此字段沒有顯式賦值，則賦值為默認值。

關(guān)鍵字 DEFAULT

1. 建表時

create table 表名稱(
字段名數(shù)據(jù)類型 primary key,
字段名數(shù)據(jù)類型 unique key not null,
字段名數(shù)據(jù)類型 unique key,
字段名數(shù)據(jù)類型 not null default 默認值,
);

2. 建表后

alter table 表名稱 modify 字段名數(shù)據(jù)類型 default 默認值;
#如果這個字段原來有非空約束，你還保留非空約束，那么在加默認值約束時，還得保留非空約束，否則非空約束就被刪除了
#同理，在給某個字段加非空約束也一樣，如果這個字段原來有默認值約束，你想保留，也要在modify語句中保留默認值約束，否則就刪除了
alter table 表名稱 modify 字段名數(shù)據(jù)類型 default 默認值 not null;

刪除默認值