MySQL表中的約束(constraint)

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

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

• 實(shí)體完整性
• 域完整性
• 引用完整性
• 用戶自定義完整性

約束？對(duì)表中字段的限制。

約束分類

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

• 單列約束
• 多列約束

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

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

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

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

如何添加約束/刪除約束

• CREATE TABLE時(shí)添加約束
• ALTER TABLE時(shí)增加約束、刪除約束

如何查看表中的約束

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

非空約束

限制某個(gè)字段/某列的值不為空

關(guān)鍵字： NOT NULL

• 默認(rèn)，所有的類型的值都可以是NULL，包括INT、FLOAT等數(shù)據(jù)類型
• 非空約束只能出現(xiàn)在表對(duì)象的列上，只能某個(gè)列單獨(dú)限定非空，不能組合非空
• 一個(gè)表可以有很多列都分別限定了非空
• 空字符串’'不等于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);

唯一性約束

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

關(guān)鍵字：UNIQUE

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

添加唯一性約束

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

可以項(xiàng)聲明未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),
#表約束實(shí)現(xiàn)多行約束
CONSTRAINT uk_user_name_pwd UNIQUE(name,password)
);

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

刪除唯一約束

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

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

主鍵約束

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

關(guān)鍵字：primary key

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

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

添加主鍵約束

CREATE TABLE test3(
id INT PRIMARY KEY, #列級(jí)約束
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)
);

刪除主鍵約束

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

ALTER TABLE test6
DROP PRIMARY KEY;

自增列-AUTO_INCREMENT

某個(gè)字段的值自增

關(guān)鍵字：auto_increment

（1）一個(gè)表最多只能有一個(gè)自增長列

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

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

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

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

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

添加自增約束

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

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約束

限定某個(gè)表的某個(gè)字段的引用完整性

關(guān)鍵字：FOREIGN KEY

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

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

（1）從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列為什么？因?yàn)楸灰蕾?被參考的值必須是唯一的

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

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

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

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

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

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

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

（9）刪除外鍵約束后，必須手動(dòng)刪除對(duì)應(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)

約束等級(jí)

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

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

刪除外鍵約束

刪除外鍵約束

ALTER TABLE emp2
DROP FOREIGN KEY fk_emp2_dept1_id;

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

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

開發(fā)場景

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

答：不是的

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

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

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

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

答：沒有

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

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

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

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

CHECK約束

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

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

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

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

DEFAULT約束

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

關(guān)鍵字 DEFAULT

1. 建表時(shí)

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

2. 建表后

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

刪除默認(rèn)值

alter table 表名稱 modify 字段名 數(shù)據(jù)類型; #刪除默認(rèn)值約束，也不保留非空約束
alter table 表名稱 modify 字段名 數(shù)據(jù)類型 not null; #刪除默認(rèn)值約束，保留非空約束

到此這篇關(guān)于MySQL數(shù)據(jù)庫表中的約束詳解的文章就介紹到這了,更多相關(guān)MySQL表中的約束內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論