MySQL表中的約束(constraint)

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

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

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

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

約束分類

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

• 單列約束
• 多列約束

根據約束的作用范圍

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

根據約束起的作用

• 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';

非空約束

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

關鍵字： NOT NULL

• 默認，所有的類型的值都可以是NULL，包括INT、FLOAT等數(shù)據類型
• 非空約束只能出現(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);

唯一性約束

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

關鍵字：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;

復合約束

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

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

刪除唯一約束

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

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

主鍵約束

用來唯一標識表中的一行記錄

關鍵字：primary key

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

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

添加主鍵約束

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)

復合主鍵約束

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

某個字段的值自增

關鍵字：auto_increment

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

（2）當需要產生唯一標識符或順序值時，可設置自增長

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

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

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

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

添加自增約束

開發(fā)中，一旦主鍵作用的字段聲明有AUTO_INCREMENT,則我們在添加數(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約束

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

關鍵字：FOREIGN KEY

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

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

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

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

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

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

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

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

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

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

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

添加外鍵約束

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

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

刪除外鍵約束

刪除外鍵約束

ALTER TABLE emp2
DROP FOREIGN KEY fk_emp2_dept1_id;

刪除外鍵約束對應索引

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

開發(fā)場景

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

答：不是的

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

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

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

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

答：沒有

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

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

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

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

CHECK約束

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

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

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

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

DEFAULT約束

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

關鍵字 DEFAULT

1. 建表時

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

2. 建表后

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

刪除默認值

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

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

最新評論