mysql約束規則有哪些及怎么創建

這篇“mysql約束規則有哪些及怎么創建”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“mysql約束規則有哪些及怎么創建”文章吧。

• 簡介

1、數據完整性（Data Integrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。它是防止數據庫中存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的
2、為了保證數據的完整性，SQL規范以約束的方式對表數據進行額外的條件限制。從以下四個方面考慮：
      實體完整性（Entity Integrity） ：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
      域完整性（Domain Integrity） ：例如：年齡范圍0-120，性別范圍“男/女”
      引用完整性（Referential Integrity） ：例如：員工所在部門，在部門表中要能找到這個部門
      用戶自定義完整性（User-defined Integrity） ：例如：用戶名唯一、密碼不能為空等，本部門經理的工資不得高于本部門職工的平均工資的5倍

• 定義

約束是表級的強制規定
可以在創建表時規定約束（通過 CREATE TABLE 語句），或者在表創建之后通過 ALTER TABLE 語句規定約束

• 分類

# 方式1
根據約束數據列的限制，約束可分為：
    單列約束：每個約束只約束一列
    多列約束：每個約束可約束多列數據

# 方式2
根據約束的作用范圍，約束可分為：
    列級約束：只能作用在一個列上，跟在列的定義后面
    表級約束：可以作用在多個列上，不與列一起，而是單獨定義

# 方式3
根據約束起的作用，約束可分為：
    NOT NULL 非空約束，規定某個字段不能為空
    UNIQUE 唯一約束，規定某個字段在整個表中是唯一的
    PRIMARY KEY 主鍵(非空且唯一)約束
    FOREIGN KEY 外鍵約束
    CHECK 檢查約束
    DEFAULT 默認值約束

• 查看表擁有的約束

# information_schema數據庫名（系統庫）
# table_constraints表名稱（專門存儲各個表的約束）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';

非空約束

• 簡介

限定某個字段/某列的值不允許為空
所有的類型的值都可以是NULL，包括INT、FLOAT等數據類型
非空約束只能出現在表對象的列上，只能某個列單獨限定非空，不能組合非空
一個表可以有很多列都分別限定了非空
空字符串''不等于NULL，0也不等于NULL

• 代碼案例

# 創建表時添加非空約束
CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);

# 添加1條數據
INSERT INTO test1(id,last_name, email, salary) VALUES(1, 'Tom', 'tom@126.com', 3400);

# 添加1條有null的數據，報錯
# 錯誤：Column 'last_name' cannot be null
INSERT INTO test1(id, last_name, email, salary) VALUES(2, NULL, 'tom1@126.com', 3400);

# 添加1條有null的數據，報錯
# 錯誤：Column 'id' cannot be null
INSERT INTO test1(id, last_name, email, salary)
VALUES(NULL, 'Jerry', 'jerry@126.com', 3400);

# 創建表之后，如果想為某個字段添加非空約束，則需要保證該字段的值都非空
INSERT INTO test1(id, email) VALUES(2, 'abc@126.com');

# 修改表時，添加約束
ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL;

# 在ALTER TABLE時刪除約束
ALTER TABLE test1 MODIFY email VARCHAR(25) NULL;

唯一性約束

• 簡介

用來限制某個字段/某列的值不能重復
同一個表可以有多個唯一約束
唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一
唯一性約束允許列值為空
在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同
MySQL會給唯一約束的列上默認創建一個唯一索引

• 代碼案例

# 在CREATE TABLE時添加約束
CREATE TABLE test2(
id INT UNIQUE, # 列級約束，指定在列上
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),
CONSTRAINT uk_test2_email UNIQUE(email) # 表級約束，在表上某1列的約束，并自定義約束別名
);

# 插入1條數據
INSERT INTO test2(id, last_name, email, salary) VALUES(1, 'Tom', 'tom@126.com', 4500);

# 如果重復了，則報錯
# 錯誤：Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id, last_name, email,salary) VALUES(1, 'Tom1', 'tom1@126.com', 4600);

# 如果重復了，則報錯
# 錯誤：Duplicate entry 'tom@126.com' for key 'test2.uk_test2_email'
INSERT INTO test2(id, last_name, email,salary) VALUES(2, 'Tom1', 'tom@126.com', 4600);

# 可以向聲明為unique的字段上添加null值
INSERT INTO test2(id, last_name, email, salary)
VALUES(2, 'Tom1', NULL, 4600);

# 而且可以多次添加null
INSERT INTO test2(id, last_name, email, salary)
VALUES(3, 'Tom2', NULL, 4600);

# 創建表后，再添加唯一性約束，保證要添加唯一性約束的字段的值都不相同后，才能添加約束
UPDATE test2
SET salary = 5000
WHERE id = 3;

# 在ALTER TABLE時添加約束
# 方式1：
ALTER TABLE test2 ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

#方式2：
ALTER TABLE test2 MODIFY last_name VARCHAR(15) UNIQUE;

# 復合的唯一性約束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`) # 表級約束，這時要2個字段合起來不重復
);

# 添加1條數據
INSERT INTO USER VALUES(1, 'Tom', 'abc');
# 再次添加1條數據，發現password重復了，但name沒有重復，則可以添加成功
INSERT INTO USER VALUES(1, 'Tom1', 'abc');

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

ALTER TABLE test2 DROP INDEX last_name;

主鍵約束

• 簡介

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

• 代碼案例

# 在CREATE TABLE時添加約束
# 一個表中最多只能有一個主鍵約束。
# 錯誤寫法：添加了2個主鍵
# Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列級約束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

# 主鍵約束特征：非空且唯一，用于唯一的標識表中的一條記錄
CREATE TABLE test4(
id INT PRIMARY KEY, #列級約束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

# MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用
CREATE TABLE test5(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
CONSTRAINT pk_test5_id PRIMARY KEY(id)  # 表級約束，沒有必要起名字
);

# 添加1條數據
INSERT INTO test4(id, last_name, salary, email) VALUES(1, 'Tom', 4500, 'tom@126.com');

# 插入數據時，主鍵重復了，報錯：Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id, last_name, salary, email) VALUES(1, 'Tom', 4500, 'tom@126.com');

# 插入數據時，主鍵為null，所以報錯：Column 'id' cannot be null
INSERT INTO test4(id, last_name, salary, email) VALUES(NULL, 'Tom', 4500, 'tom@126.com');

# 復合主鍵約束
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);
#如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
INSERT INTO user1
VALUES(1, 'Tom', 'abc');

# 插入第2條數據，password重復了，但name沒有重復
INSERT INTO user1 VALUES(1, 'Tom1', 'abc');
# 插入1條數據，name和password和起來重復了，所以錯誤：Column 'name' cannot be null
INSERT INTO user1 VALUES(1, NULL, 'abc');

# 在ALTER TABLE時添加約束
CREATE TABLE test6(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

# 添加主鍵約束
ALTER TABLE test6
ADD PRIMARY KEY (id);

# 刪除主鍵約束
ALTER TABLE test6 DROP PRIMARY KEY;

自增約束

• 簡介

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

• 代碼案例

# 在CREATE TABLE時添加
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15) 
);

# 開發中，一旦主鍵作用的字段上聲明有AUTO_INCREMENT，則我們在添加數據時，就不要給主鍵對應的字段去賦值了
INSERT INTO test7(last_name) VALUES('Tom');

#當我們向主鍵（含AUTO_INCREMENT）的字段上添加0 或 null時，實際上會自動的往上添加指定的字段的數值
INSERT INTO test7(id, last_name) VALUES(0, 'Tom');

INSERT INTO test7(id, last_name) VALUES(NULL, 'Tom');

# 自定義id時，不會使用自定義的id
INSERT INTO test7(id, last_name) VALUES(10, 'Tom');

# 在ALTER TABLE 時添加
CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(15) 
);

# 添加自增約束 
ALTER TABLE test8 MODIFY id INT AUTO_INCREMENT;

# 在ALTER TABLE 時刪除
ALTER TABLE test8 MODIFY id INT;

• MySQL8.0新特性—自增變量的持久化

# 在MySQL 5.7中演示
# 創建表，并設置為自增
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);
# 插入4條數據
INSERT INTO test9
VALUES(0),(0),(0),(0);
# 刪除第4條數據
DELETE FROM test9
WHERE id = 4;
# 再插入1條數據，發現是從5開始自增的
INSERT INTO test9
VALUES(0);
# 刪除第5條數據
DELETE FROM test9
WHERE id = 5;
# 重啟服務器
# 再插入1條數據，發現是從4開始自增的
INSERT INTO test9
VALUES(0);
# 這是因為，在MySQL 5.7系統中，對于自增主鍵的分配規則，是由InnoDB數據字典內部一個 計數器 來決定的，而該計數器只在 內存中維護 ，并不會持久化到磁盤中。當數據庫重啟時，該
計數器會被初始化

#在MySQL 8.0中演示
# 創建表，并設置為自增
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);
# 插入4條數據
INSERT INTO test9
VALUES(0),(0),(0),(0);
# 刪除第4條記錄
DELETE FROM test9
WHERE id = 4;
# 再插入1條記錄，發現是從5開始自增的
INSERT INTO test9
VALUES(0);
# 刪除第5條記錄
DELETE FROM test9
WHERE id = 5;
#重啟服務器
# 再插入1條記錄，發現是從6開始自增的
INSERT INTO test9
VALUES(0);
# 這是因為，MySQL 8.0將自增主鍵的計數器持久化到 重做日志 中。每次計數器發生改變，都會將其寫入重做日志中。如果數據庫重啟，InnoDB會根據重做日志中的信息來初始化計數器的內存值

外鍵約束

• 簡介

限定某個表的某個字段的引用完整性
主表（父表）：被引用的表，被參考的表
從表（子表）：引用別人的表，參考別人的表

從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列
在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名（例如student_ibfk_1;），也可以指定外鍵約束名
創建(CREATE)表時就指定外鍵約束的話，先創建主表，再創建從表
刪表時，先刪從表（或先刪除外鍵約束），再刪除主表
當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然后才可以刪除主表的數據
在“從表”中指定外鍵約束，并且一個表可以建立多個外鍵約束
從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣，邏輯意義一致。如果類型不一樣，創建子表時，就會出現錯誤“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”
當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據外鍵查詢效率很高）
刪除外鍵約束后，必須 手動 刪除對應的索引

• 代碼案例

# 先創建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);

# 操作報錯，因為主表中的dept_id上沒有主鍵約束或唯一性約束
# 再創建從表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id) # 表級約束，指定外鍵
);

# 主表添加主鍵約束
ALTER TABLE dept1 ADD PRIMARY KEY (dept_id);

# 再創建從表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id) # 表級約束
);

# 演示外鍵的效果
# 添加失敗，因為外鍵的值在主表中不存在
INSERT INTO emp1
VALUES(1001, 'Tom', 10);

# 主表中添加數據
INSERT INTO dept1 VALUES(10, 'IT');
# 在主表dept1中添加了10號部門以后，我們就可以在從表中添加10號部門的員工
INSERT INTO emp1 VALUES(1001, 'Tom', 10);

# 刪除失敗，從表中有該值的關聯數據
DELETE FROM dept1 WHERE dept_id = 10;

# 更新失敗，從表中有該值的關聯數據
UPDATE dept1 
SET dept_id = 20
WHERE dept_id = 10;

# 在ALTER TABLE時添加外鍵約束
# 創建表1
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
# 創建表2
CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);
# 添加約束
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(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不能識別

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

• 代碼案例

# 創建表1
CREATE TABLE dept(
    did INT PRIMARY KEY,        #部門編號
    dname VARCHAR(50)           #部門名稱
);
# 創建表2
CREATE TABLE emp(
    eid INT PRIMARY KEY,  #員工編號
    ename VARCHAR(5),     #員工姓名
    deptid INT,       #員工所在的部門
    FOREIGN KEY (deptid) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE SET NULL # 把修改操作設置為級聯修改等級，把刪除操作設置為set null等級
);
# 主表插入數據
INSERT INTO dept VALUES(1001,'教學部');
INSERT INTO dept VALUES(1002, '財務部');
INSERT INTO dept VALUES(1003, '咨詢部');
# 從表插入數據
INSERT INTO emp VALUES(1,'張三',1001); #在添加這條記錄時，要求部門表有1001部門
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);
# 修改時級聯修改
UPDATE dept
SET did = 1004
WHERE did = 1002;
# 刪除時設置為null
DELETE FROM dept
WHERE did = 1004;

• 刪除外鍵約束

ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept_id;

#再手動的刪除外鍵約束對應的普通索引
SHOW INDEX FROM emp1;

ALTER TABLE emp1 DROP INDEX fk_emp1_dept_id;

• 小結

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

• 阿里開發規范

【 強制 】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。
說明：（概念解釋）學生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用于 單機低并發 ，不適合 分布式 、 高并發集群 ；級聯更新是強阻塞，存在數據庫 更新風暴 的風險；外鍵影響數據庫的 插入速度

檢查約束

• 簡介

檢查某個字段的值是否符號xx要求，一般指的是值的范圍
MySQL5.7 可以使用check約束，但check約束對數據驗證沒有任何作用。添加數據時，沒有任何錯誤或警告
MySQL 8.0中可以使用check約束了

• 代碼案例

# 創建表，并指定約束
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);
# 添加1條數據
INSERT INTO test10
VALUES(1, 'Tom', 2500);
#添加失敗
INSERT INTO test10
VALUES(2, 'Tom1', 1500);

默認約束

• 簡介

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

• 代碼案例

# 在CREATE TABLE添加約束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
# 添加數據時有值，則不使用默認值
INSERT INTO test11(id,last_name,salary) VALUES(1, 'Tom', 3000);
# 使用默認值
INSERT INTO test11(id, last_name) VALUES(2, 'Tom1');

# 在ALTER TABLE添加約束
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);

ALTER TABLE test12 MODIFY salary DECIMAL(8,2) DEFAULT 2500;

# 在ALTER TABLE刪除約束
ALTER TABLE test12 MODIFY salary DECIMAL(8,2);

以上就是關于“mysql約束規則有哪些及怎么創建”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。