溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL約束知識點實例分析”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL約束知識點實例分析”文章能幫助大家解決問題。
數據完整性（Data Integrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。它是防止數據庫中 存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的。
為了保證數據的完整性，SQL 規范以約束的方式對 表數據進行額外的條件限制 。從以下四個方面考慮：
實體完整性（Entity Integrity ） ：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
域完整性（Domain Integrity ） ：例如：年齡范圍 0-120 ，性別范圍 “ 男 / 女 ”
引用完整性（Referential Integrity ） ：例如：員工所在部門，在部門表中要能找到這個部門
用戶自定義完整性（User - defined Integrity ） ：例如：用戶名唯一、密碼不能為空等，本部門經理的工資不得高于本部門職工的平均工資的5 倍。
約束是表級的強制規定。
可以在 創建表時規定約束（通過 CREATE TABLE 語句） ，或者在 表創建之后通過 ALTER TABLE 語句規定
約束 。
根據約束數據列的限制， 約束可分為：
單列約束 ：每個約束只約束一列
多列約束 ：每個約束可約束多列數據
根據約束的作用范圍 ，約束可分為：
列級約束 ：只能作用在一個列上，跟在列的定義后面
表級約束 ：可以作用在多個列上，不與列一起，而是單獨定義
位置 | 支持的約束類型 | 是否可以起約束名 | |
列級約束： | 列的后面 | 語法都支持，但外鍵沒有效果 | 不可以 |
表級約束： | 所有列的下面 | 默認和非空不支持，其他支持 | 可以（主鍵沒有效果） |
根據約束起的作用
，約束可分為：
1. NOT NULL 非空約束，規定某個字段不能為空
2. UNIQUE 唯一約束，規定某個字段在整個表中是唯一的
3. PRIMARY KEY 主鍵(非空且唯一)約束
4. FOREIGN KEY 外鍵約束
5. CHECK 檢查約束
6. DEFAULT 默認值約束
注意： MySQL不支持check約束，但可以使用check約束，而沒有任何效果
查看某個表已有的約束 ：
# information_schema數據庫名（系統庫）
# table_constraints表名稱（專門存儲各個表的約束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名稱';
限定某個字段/ 某列的值不允許為空
NOT NULL
1. 默認，所有的類型的值都可以是NULL，包括INT、FLOAT等數據類型
2. 非空約束只能出現在表對象的列上，只能某個列單獨限定非空，不能組合非空
（只有列級約束，沒有表級約束）
3. 一個表可以有很多列都分別限定了非空
4. 空字符串''不等于NULL，0也不等于NULL
語法格式：
CREATE TABLE 表名稱( 字段名 數據類型, 字段名 數據類型 NOT NULL, 字段名 數據類型 NOT NULL );
舉例：
語法格式：
alter table 表名稱 modify 字段名 數據類型 not null ;
舉例：
語法格式：
1.alter table 表名稱 modify 字段名 數據類型 NULL; #去掉not null，相當于修改某個非注解字段，該字段允 許為空 2.alter table 表名稱 modify 字段名 數據類型; #去掉not null，相當于修改某個非注解字段，該字段允許為空
舉例：
用來限制某個字段/ 某列的值不能重復。
UNIQUE
1.同一個表可以有多個唯一約束。
2.唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
3.唯一性約束允許列值為空。并且允許存在多個NULL值。
4.在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同。
5.MySQL會給唯一約束的列上默認創建一個唯一索引。
語法格式：
1.列級約束
create table 表名稱( 字段名 數據類型, 字段名 數據類型 unique, 字段名 數據類型 unique key, 字段名 數據類型 );
2.表級約束
create table 表名稱( 字段名 數據類型, 字段名 數據類型, 字段名 數據類型, [constraint 約束名] unique key(字段名) );
舉例：
語法格式：
1. alter table 表名稱 add [constraint 約束名] unique key(字段列表); 2. alter table 表名稱 modify 字段名 字段類型 unique;
注：字段列表中如果是一個字段，表示該列的值唯一。如果是兩個或更多個字段，那么復合唯一，即多個字段的組合是唯一的
舉例：
語法格式：
1.在 create table 時添加復合唯一約束
create table 表名稱( 字段名 數據類型, 字段名 數據類型, 字段名 數據類型, [constraint 約束名] unique key(字段列表) );
2. 在 alter table 時添加復合唯一約束
alter table 表名稱 add [constraint 約束名] unique key(字段列表);
字段列表中寫的是多個字段名，多個字段名用逗號分隔，表示那么是復合唯一，即多
個字段的組合是唯一的
舉例：
1.添加唯一性約束的列上也會自動創建唯一索引。
2.刪除唯一約束只能通過刪除唯一索引的方式刪除。
3.刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
4.如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；
如果是組合列，那么默認和() 中排在第一個的列名相同。
也可以是自定義唯一性約束名。
語法格式：
ALTER TABLE USER DROP INDEX 約束名;
查看表從索引：
show index from 表名稱 ;
舉例：
用來唯一標識表中的一行記錄。
primary key
主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值。
1. 一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別創建，也可以在表級別上創建。
2. 主鍵約束對應著表中的一列或者多列（復合主鍵）
3. 如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
4. MySQL的主鍵名總是 PRIMARY ，就算自己命名了主鍵約束名也沒用。
5. 當創建主鍵約束時，系統默認會在所在的列或列組合上建立對應的 主鍵索引 （能夠根據主鍵查詢的，就根據主鍵查詢，效率更高）。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。
6. 需要注意的一點是，不要修改主鍵字段的值。因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性
語法格式：
1.列級模式
create table 表名稱( 字段名 數據類型 primary key, #列級模式 字段名 數據類型, 字段名 數據類型 );
2.表級模式（注： MySQL的主鍵名總是 PRIMARY ，就算自定義了主鍵約束名也沒用 ）
create table 表名稱( 字段名 數據類型, 字段名 數據類型, 字段名 數據類型, [constraint 約束名] primary key(字段名) #表級模式 );
舉例：
多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
語法格式：
create table 表名稱( 字段名 數據類型, 字段名 數據類型, 字段名 數據類型, primary key(字段名1,字段名2) );
字段1和字段2的組合是唯一的，也可以有更多個字段
舉例：
字段列表可以是一個字段，也可以是多個字段，如果是多個字段的話，是復合主鍵
語法格式：
1. ALTER TABLE 表名稱 MODIFY 字段名 數據類型 PRIMARY KEY; 2. ALTER TABLE 表名稱 ADD PRIMARY KEY(字段列表);
舉例：
刪除主鍵約束，不需要指定主鍵名，因為一個表只有一個主鍵， 刪除主鍵約束后，非空還存在。 （但在實際開發中，不會去刪除表中的主鍵約束）
語法格式：
alter table 表名稱 drop primary key;
舉例：
某個字段的值自增
auto_increment
1. 一個表最多只能有一個自增長列
2. 當需要產生唯一標識符或順序值時，可設置自增長
3. 自增長列約束的列必須是鍵列（主鍵列，唯一鍵列）
4. 自增約束的列的數據類型必須是整數類型
5. 如果自增列指定了 0 和 null，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接 賦值為具體值
語法格式：
create table 表名稱( 字段名 數據類型 primary key auto_increment, 字段名 數據類型 , 字段名 數據類型 , 字段名 數據類型 ); create table 表名稱( 字段名 數據類型 , 字段名 數據類型 unique key auto_increment, 字段名 數據類型 );
舉例：
非法創建：
正確創建方式：
插入數據:
特殊情況(不推薦此寫法)：
語法格式：
alter table 表名稱 modify 字段名 數據類型 auto_increment;
舉例：
語法格式：
alter table 表名稱 modify 字段名 數據類型;
舉例：
在MySQL 8.0 之前，自增主鍵 AUTO_INCREMENT 的值如果大于 max(primary key)+1 ，在 MySQL 重啟后，會重 置AUTO_INCREMENT=max(primary key)+1 ，這種現象在某些情況下會導致業務主鍵沖突或者其他難以發 現的問題。 下面通過案例來對比不同的版本中自增變量是否持久化。
案例：
對于MySQL5.7版本：
然后重啟MySQL57服務器：（以管理員的身份運行）
從結果可以看出，新插入的0 值分配的是 4 ，按照重啟前的操作邏輯，此處應該分配 6 。出現上述結果的主要原因是自增主鍵沒有持久化。
在MySQL 5.7系統中，對于自增主鍵的分配規則，是由 InnoDB 數據字典內部一個 計數器 來決定的，而該計數器只在 內存中維護 ，并不會持久化到磁盤中。當數據庫重啟時，該計數器會被初始化。
對于MySQL8.0版本：
然后重啟MySQL80服務器：（以管理員的身份運行）
從結果可以看出，自增變量已經持久化了。
MySQL 8.0將自增主鍵的計數器持久化到 重做日志 中。每次計數器發生改變，都會將其寫入重做日志中。如果數據庫重啟，InnoDB 會根據重做日志中的信息來初始化計數器的內存值。
限定某個表的某個字段的引用完整性。
比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。
FOREIGN KEY
主表（父表）：被引用的表，被參考的表
從表（子表）：引用別人的表，參考別人的表
例如：員工表的員工所在部門這個字段的值要參考部門表：部門表是主表，員工表是從表。
例如：學生表、課程表、選課表：選課表的學生和課程要分別參考學生表和課程表，學生表和課程表是主表，選課表是從表。
1. 從表的外鍵列， 必須引用/參考主表的主鍵或唯一約束的列
因為被依賴/被參考的值必須是唯一的
2. 在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名（例如 student_ibfk_1;），也可以指定外鍵約束名。
3. 創建(CREATE)表時就指定外鍵約束的話，先創建主表，再創建從表
4. 刪表時，先刪從表（或先刪除外鍵約束），再刪除主表
5. 當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴 該記錄的數據，然后才可以刪除主表的數據
6. 在“從表”中指定外鍵約束，并且一個表可以建立多個外鍵約束
7. 從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣，邏輯意義一致。如果類型不一樣，創建子表時，就會出現錯誤。
8. 當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據外鍵查詢效率很高）
9. 刪除外鍵約束后，必須 手動 刪除對應的索引
語法格式：
create table 主表名稱( 字段1 數據類型 primary key, 字段2 數據類型 ); create table 從表名稱( 字段1 數據類型 primary key, 字段2 數據類型, [CONSTRAINT <外鍵約束名稱>] FOREIGN KEY（從表的某個字段) references 主表名(被參考字段) [on update xx][on delete xx]; );
-- FOREIGN KEY: 在表級指定子表中的列
-- REFERENCES: 標示在父表中的列
(從表的某個字段)的數據類型必須與主表名(被參考字段)的數據類型一致，邏輯意義也一樣
(從表的某個字段)的字段名可以與主表名(被參考字段)的字段名一樣，也可以不一樣
舉例：
正確的創建方式：
錯誤的創建方式：
添加數據：
修改數據：
刪除數據：
一般情況下，表與表的關聯都是提前設計好了的，因此，會在創建表的時候就把外鍵約束定義好。不過，如果需要修改表的設計（比如添加新的字段，增加新的關聯關系），但沒有預先定義外鍵約束，那么，就要用修改表的方式來補充定義。
語法格式：
ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的字段) REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];
1. Cascade 方式 ：在父表上 update/delete 記錄時，同步 update/delete 掉子表的匹配記錄
2. Set null方式 ：在父表上 update/delete 記錄時，將子表上匹配記錄的列設為 null ，但是要注意子 表的外鍵列不能為not null
3. No action方式 ：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行 update/delete 操作
4 .Restrict方式 （默認） ：同no action ， 都是立即檢查外鍵約束
5. Set default方式 （在可視化工具 SQLyog 中可能顯示空白）：父表有變更時，子表將外鍵列設置成一個默認的值，但Innodb 不能識別
如果沒有指定等級，就相當于Restrict方式。
對于外鍵約束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
舉例：（以 on update cascade on delete set null 為例）
1.創建表
2.添加數據
3.修改數據
4.刪除數據
流程如下：
(1)第一步先查看約束名和刪除外鍵約束
#查看某個表的約束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱'; ALTER TABLE 從表名 DROP FOREIGN KEY 外鍵約束名;
（ 2）第二步查看索引名和刪除索引。（注意，只能手動刪除）
#查看某個表的索引名
SHOW INDEX FROM 表名稱; ALTER TABLE 從表名 DROP INDEX 索引名;
注意： 刪除外鍵約束后，必須 手動 刪除對應的索引
舉例：
問題 1 ：如果兩個表之間有關系（一對一、一對多），比如：員工表和部門表（一對多），它們之間是否 一定要建外鍵約束？
答：不是的
問題 2 ：建和不建外鍵約束有什么區別？
答：建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）會受到限制，從語法層面受到限制。
例如：在員工表中不可能添加一個員工信息，它的部門的值在部門表中找不到。
不建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）不受限制，要保證數據的 引用完整 性 ，只能依 靠程序員的自覺 ，或者是 在 Java 程序中進行限定 。例如：在員工表中，可以添加一個員工的信息，它的部門指定為一個完全不存在的部門。
問題 3 ：那么建和不建外鍵約束和查詢有沒有關系？
答：沒有
拓展：
在 MySQL 里，外鍵約束是有成本的，需要消耗系統資源。對于大并發的 SQL 操作，有可能會不適 合。比如大型網站的中央數據庫，可能會 因為外鍵約束的系統開銷而變得非常慢 。所以， MySQL 允 許你不使用系統自帶的外鍵約束，在 應用層面 完成檢查數據一致性的邏輯。也就是說，即使你不 用外鍵約束，也要想辦法通過應用層面的附加邏輯，來實現外鍵約束的功能，確保數據的一致性。
【 強制 】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。
說明：（概念解釋）學生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學 生表中的 student_id ，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用于 單機低并發 ，不適合 分布式 、 高并發集群 ；級聯更新是強阻塞，存在數據庫 更新風暴 的風險；外鍵影響 數據庫的 插入速度 。
檢查某個字段的值是否符號xx 要求，一般指的是值的范圍
CHECK
MySQL5.7 可以使用 check約束，但check約束對數據驗證沒有任何作用。添加數據時，沒有任何錯誤或警告。
MySQL 8.0中可以使用check約束了 。
給某個字段/ 某列指定默認值，一旦設置默認值，在插入數據時，如果此字段沒有顯式賦值，則賦值為默認值。
DEFAULT
語法格式：
方式1：
create table 表名稱( 字段名 數據類型 primary key, 字段名 數據類型 unique key not null, 字段名 數據類型 unique key, 字段名 數據類型 not null default 默認值, );
方式2：
create table 表名稱( 字段名 數據類型 default 默認值 , 字段名 數據類型 not null default 默認值, 字段名 數據類型 not null default 默認值, primary key(字段名), unique key(字段名) );
說明：默認值約束一般不在唯一鍵和主鍵列上加
舉例：
語法格式：
alter table 表名稱 modify 字段名 數據類型 default 默認值; alter table 表名稱 modify 字段名 數據類型 default 默認值 not null ;
注：
1.如果這個字段原來有非空約束，你還保留非空約束，那么在加默認值約束時，還得保留非空約束，否則非空約束就被刪除了。
2.同理，在給某個字段加非空約束也一樣，如果這個字段原來有默認值約束，你想保留，也要在modify語句中保留默認值約束，否則就刪除了。
舉例：
語法格式：
1.刪除默認值約束，也不保留非空約束
alter table 表名稱 modify 字段名 數據類型 ;
2.刪除默認值約束，保留非空約束
alter table 表名稱 modify 字段名 數據類型 not null ;
舉例：
面試 1 、為什么建表時，加 not null default '' 或 default 0
答：不想讓表中出現null 值。
面試 2 、為什么不想要 null 的值
答: （1）不好比較。 null 是一種特殊值，比較時只能用專門的 is null 和 is not null 來比較。碰到運算符，通常返回null 。
（2）效率不高。影響提高索引效果。因此，我們往往在建表時 not null default '' 或 default 0
面試 3 、帶 AUTO_INCREMENT 約束的字段值是從 1 開始的嗎？
在MySQL 中，默認AUTO_INCREMENT的初始 值是1，每新增一條記錄，字段值自動加1 。設置自增屬性（AUTO_INCREMENT）的時候，還可以指定第 一條插入記錄的自增字段的值，這樣新插入的記錄的自增字段值從初始值開始遞增，如在表中插入第一 條記錄，同時指定id 值為 5 ，則以后插入的記錄的 id 值就會從 6 開始往上增加。添加主鍵約束時，往往需要 設置字段自動增加屬性。
面試 4 、并不是每個表都可以任意選擇存儲引擎？ 外鍵約束（
FOREIGN KEY）不能跨引擎使用。(主表和從表用的引擎要相同)
MySQL支持多種存儲引擎，每一個表都可以指定一個不同的存儲引擎，需要注意的是：外鍵約束是用來保證數據的參照完整性的，如果表之間需要關聯外鍵，卻指定了不同的存儲引擎，那么這些表之間是不能創建外鍵約束的。所以說，存儲引擎的選擇也不完全是隨意的。
關于“MySQL約束知識點實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
