MySQL資料庫表中的約束詳解

2023-03-02 18:01:36

MySQL表中的約束(constraint)

為了保證資料的完整性，（資料的精確性和可靠性）

SQL規範以約束的方式對錶資料進行額外的條件限制，可從以下四個方面進行考慮

實體完整性
域完整性
參照完整性
使用者自定義完整性

約束？對錶中欄位的限制。

約束分類

約束作用欄位的個數

單列約束
多列約束

根據約束的作用範圍

列級約束，將此約束宣告放在對應欄位的後面
表級約束：在表中所有欄位宣告完，在所有欄位的後面宣告約束

根據約束起的作用

NOT NULL 非空約束，規定某個欄位不能為空
UNIQUE 唯一約束，規定某個欄位在整個表中是唯一的
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等資料型別
非空約束只能出現在表物件的列上，只能某個列單獨限定非空，不能組合非空
一個表可以有很多列都分別限定了非空
空字串’'不等於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

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

新增唯一性約束

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),
#表約束實現多行約束
CONSTRAINT uk_user_name_pwd UNIQUE(name,password)
);

create table 表名稱(
欄位名資料型別,
欄位名資料型別,
欄位名資料型別,
unique key(欄位列表) #欄位列表中寫的是多個欄位名，多個欄位名用逗號分隔，表示那麼是複合唯一，即多
個欄位的組合是唯一的
);

刪除唯一約束

新增唯一性約束的列上也會自動建立唯一索引。
刪除唯一約束只能通過刪除唯一索引的方式刪除。
刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
如果建立唯一約束時未指定名稱，如果是單列，就預設和列名相同；
如果是組合列，那麼預設和() 中排在第一個的列名相同。也可以自定義唯一性約束

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

主鍵約束

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

關鍵字：primary key

主鍵約束相當於唯一約束+非空約束的組合，主鍵約束列不允許重複，也不允許出現空值

一個表最多隻能有一個主鍵約束，建立主鍵約束可以在列級別建立，也可以在表級別上建立。
主鍵約束對應著表中的一列或者多列（複合主鍵）
如果是多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重複。
MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用。
當建立主鍵約束時，系統預設會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。
需要注意的一點是，不要修改主鍵欄位的值。因為主鍵是資料記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞資料的完整性。

新增主鍵約束

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)
);

刪除主鍵約束

在實際開發中根本不會這樣做

ALTER TABLE test6
DROP PRIMARY KEY;

自增列-AUTO_INCREMENT

某個欄位的值自增

關鍵字：auto_increment

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

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

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

（4）自增約束的列的資料型別必須是整數型別

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

當向主鍵（含AUTO_INCREMENT）的字元上新增0或null時，實際上會自動的往上新增指定欄位的數值

新增自增約束

開發中，一旦主鍵作用的欄位宣告有AUTO_INCREMENT,則我們在新增資料時，就不要給主鍵賦值了

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）在建立外來鍵約束時，如果不給外來鍵約束命名，預設名不是列名，而是自動產生一個外來鍵名（例如 student_ibfk_1;），也可以指定外來鍵約束名。

（3）建立(CREATE)表時就指定外來鍵約束的話，先建立主表，再建立從表

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

（5）當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除資料，需要先刪除從表中依賴該記錄的資料，然後才可以刪除主表的資料

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

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

（8）當建立外來鍵約束時，系統預設會在所在的列上建立對應的普通索引。但是索引名是外來鍵的約束名。（根據外來鍵查詢效率很高）

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

新增外來鍵約束

建立主表

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

建立從表

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;

開發場景

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

答：不是的

問題2：建和不建外來鍵約束有什麼區別？

答：建外來鍵約束，你的操作（建立表、刪除表、新增、修改、刪除）會受到限制，從語法層面受到限制。例如：在員工表中不可能新增一個員工資訊，它的部門的值在部門表中找不到。

不建外來鍵約束，你的操作（建立表、刪除表、新增、修改、刪除）不受限制，要保證資料的參照完整性，只能依靠程式設計師的自覺，或者是在Java程式中進行限定。例如：在員工表中，可以新增一個員工的資訊，它的部門指定為一個完全不存在的部門。

問題3：那麼建和不建外來鍵約束和查詢有沒有關係？

答：沒有

在 MySQL 裡，外來鍵約束是有成本的，需要消耗系統資源。對於大並行的 SQL 操作，有可能會不適合。比如大型網站的中央資料庫，可能會因為外來鍵約束的系統開銷而變得非常慢。所以， MySQL 允許你不使用系統自帶的外來鍵約束，在應用層面完成檢查資料一致性的邏輯。也就是說，即使你不用外來鍵約束，也要想辦法通過應用層面的附加邏輯，來實現外來鍵約束的功能，確保資料的一致性。

阿里開發規範

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

說明：（概念解釋）學生表中的 student_id 是主鍵，那麼成績表中的 student_id 則為外來鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外來鍵與級聯更新適用於單機低並行，不適合分散式、高並行叢集；級聯更新是強阻塞，存在資料庫更新風暴的風險；外來鍵影響資料庫的插入速度。

CHECK約束

檢查某個欄位的值是否符合xxx要求，一般指值的範圍

MySQL5.7 可以使用check約束，但check約束對資料驗證沒有任何作用。新增資料時，沒有任何錯誤或警告

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

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

DEFAULT約束

給某個欄位/某列指定預設值，一旦設定預設值，在插入資料時，如果此欄位沒有顯式賦值，則賦值為預設值。

關鍵字 DEFAULT

1. 建表時

create table 表名稱(
欄位名資料型別 primary key,
欄位名資料型別 unique key not null,
欄位名資料型別 unique key,
欄位名資料型別 not null default 預設值,
);

2. 建表後

alter table 表名稱 modify 欄位名資料型別 default 預設值;
#如果這個欄位原來有非空約束，你還保留非空約束，那麼在加預設值約束時，還得保留非空約束，否則非空約束就被刪除了
#同理，在給某個欄位加非空約束也一樣，如果這個欄位原來有預設值約束，你想保留，也要在modify語句中保留預設值約束，否則就刪除了
alter table 表名稱 modify 欄位名資料型別 default 預設值 not null;

刪除預設值