範例解析MySQL約束知識點

本篇文章給大家帶來了關於的相關知識，其中主要整理了約束的相關問題，約束為了保證資料的完整性，資料完整性是指資料的精確性和可靠性，它是防止資料庫中存在不符合語意規定的資料和防止因錯誤資訊的輸入輸出造成無效操作或錯誤資訊而提出的，下面一起來看一下，希望對大家有幫助。

推薦學習：

1. 約束(constraint)概述

1.1 為什麼需要約束 -- 為了保證資料的完整性

資料完整性（Data Integrity）是指資料的精確性（Accuracy）和可靠性（Reliability）。它是防止資料庫中存在不符合語意規定的資料和防止因錯誤資訊的輸入輸出造成無效操作或錯誤資訊而提出的。

為了保證資料的完整性，SQL 規範以約束的方式對表資料進行額外的條件限制。從以下四個方面考慮：

實體完整性（Entity Integrity ）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄

域完整性（Domain Integrity ）：例如：年齡範圍 0-120 ，性別範圍「男 / 女」

參照完整性（Referential Integrity ）：例如：員工所在部門，在部門表中要能找到這個部門

使用者自定義完整性（User - defined Integrity ）：例如：使用者名稱唯一、密碼不能為空等，本部門經理的工資不得高於本部門職工的平均工資的5 倍。

1.2 什麼是約束 -- 對錶中欄位的限制

約束是表級的強制規定。

可以在建立表時規定約束（通過 CREATE TABLE 語句），或者在表建立之後通過 ALTER TABLE 語句規定

約束。

1.3 約束的分類

根據約束資料列的限制，約束可分為：

單列約束：每個約束只約束一列

多列約束：每個約束可約束多列資料

根據約束的作用範圍，約束可分為：

列級約束：只能作用在一個列上，跟在列的定義後面

表級約束：可以作用在多個列上，不與列一起，而是單獨定義

	位置	支援的約束型別	是否可以起約束名
列級約束：	列的後面	語法都支援，但外來鍵沒有效果	不可以
表級約束：	所有列的下面	預設和非空不支援，其他支援	可以（主鍵沒有效果）

根據約束起的作用，約束可分為：

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 = '表名稱';

2. 非空約束（NOT NULL）

2.1 作用

限定某個欄位/ 某列的值不允許為空

2.2 關鍵字

NOT NULL

2.3 特點

1. 預設，所有的型別的值都可以是NULL，包括INT、FLOAT等資料型別

2. 非空約束只能出現在表物件的列上，只能某個列單獨限定非空，不能組合非空

（只有列級約束，沒有表級約束）

3. 一個表可以有很多列都分別限定了非空

4. 空字串''不等於NULL，0也不等於NULL

2.4 新增非空約束

2.4.1 在CREATE TABLE 時新增非空約束

語法格式：

        CREATE TABLE 
   表名稱( 
  
                欄位名 資料型別, 
  
                欄位名 資料型別 NOT NULL, 
  
                欄位名 資料型別 NOT NULL 
  
        );

舉例：

2.4.2 在ALTER TABLE 時新增非空約束

語法格式：

        
alter table 
表名稱 
modify 
欄位名 資料型別 
not 
null
;

舉例：

2.5 刪除非空約束

語法格式：

        1.alter table 
   表名稱 modify 欄位名 資料型別 NULL; #去掉not null，相當於修改某個非註解欄位，該欄位允 許為空 
  
        2.alter table 表名稱 modify 欄位名 資料型別; #去掉not null，相當於修改某個非註解欄位，該欄位允許為空

舉例：

3. 唯一性約束(UNIQUE 或 UNIQUE KEY)

3.1 作用

用來限制某個欄位/ 某列的值不能重複。

3.2 關鍵字

UNIQUE

3.3 特點

1.同一個表可以有多個唯一約束。

2.唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。

3.唯一性約束允許列值為空。並且允許存在多個NULL值。

4.在建立唯一約束的時候，如果不給唯一約束命名，就預設和列名相同。

5.MySQL會給唯一約束的列上預設建立一個唯一索引。

3.4 新增唯一約束

3.4.1 在CREATE TABLE 時新增唯一約束

語法格式：

1.列級約束

        create table 
   表名稱( 
  
        欄位名 資料型別, 
  
        欄位名 資料型別 unique, 
  
        欄位名 資料型別 unique key, 
  
        欄位名 資料型別 
  
        );

2.表級約束

        create table 
   表名稱( 
  
        欄位名 資料型別, 
  
        欄位名 資料型別, 
  
        欄位名 資料型別, 
  
        [constraint 約束名] unique key(欄位名) 
  
        );

舉例：

3.4.2 在ALTER TABLE 時新增唯一約束

語法格式：

          1. alter table 表名稱
            add  [constraint 約束名] unique key(欄位列表);
        2. alter table 表名稱
            modify 欄位名 欄位型別 unique;

注：欄位列表中如果是一個欄位，表示該列的值唯一。如果是兩個或更多個欄位，那麼複合唯一，即多個欄位的組合是唯一的

舉例：

3.4.3 新增複合唯一性約束

語法格式：

1.在 create table 時新增複合唯一約束

                create table 表名稱( 
  
                欄位名 資料型別, 
  
                欄位名 資料型別, 
  
                欄位名 資料型別, 
  
                 [constraint 約束名] unique key(欄位列表) 
  
                );

2. 在 alter table 時新增複合唯一約束

                alter table 表名稱 
    
                add  [constraint 約束名] unique key(欄位列表);

欄位列表中寫的是多個欄位名，多個欄位名用逗號分隔，表示那麼是複合唯一，即多

個欄位的組合是唯一的

舉例：

3.5 刪除唯一約束

1.新增唯一性約束的列上也會自動建立唯一索引。

2.刪除唯一約束只能通過刪除唯一索引的方式刪除。

3.刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。

4.如果建立唯一約束時未指定名稱，如果是單列，就預設和列名相同；

如果是組合列，那麼預設和() 中排在第一個的列名相同。

也可以是自定義唯一性約束名。

語法格式：

        ALTER TABLE USER 
  
        DROP INDEX 約束名;

檢視表從索引：

        show index from 表名稱
     ;

舉例：

4. PRIMARY KEY 約束（主鍵約束）

4.1 作用

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

4.2 關鍵字

primary key

4.3 特點

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

1. 一個表最多隻能有一個主鍵約束，建立主鍵約束可以在列級別建立，也可以在表級別上建立。

2. 主鍵約束對應著表中的一列或者多列（複合主鍵）

3. 如果是多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重複。

4. MySQL的主鍵名總是 PRIMARY ，就算自己命名了主鍵約束名也沒用。

5. 當建立主鍵約束時，系統預設會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高）。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。

6. 需要注意的一點是，不要修改主鍵欄位的值。因為主鍵是資料記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞資料的完整性

4.4 新增主鍵約束

4.4.1 在CREATE TABLE 時新增主鍵約束

語法格式：

1.列級模式

                create table 表名稱( 
  
                欄位名 資料型別 primary key, #列級模式 
  
                欄位名 資料型別, 
  
                欄位名 資料型別 
  
                );

2.表級模式（注： MySQL的主鍵名總是 PRIMARY ，就算自定義了主鍵約束名也沒用）

                create table 表名稱( 
  
                欄位名 資料型別, 
  
                欄位名 資料型別, 
  
                欄位名 資料型別, 
  
                [constraint 約束名] primary key(欄位名) #表級模式 
  
                );

舉例：

4.4.2 在CREATE TABLE 時新增複合主鍵約束

多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重複。

語法格式：

        create table 
    表名稱( 
  
        欄位名 資料型別, 
  
        欄位名 資料型別, 
  
        欄位名 資料型別, 
  
        primary key(欄位名1,欄位名2) 
  
        );

欄位1和欄位2的組合是唯一的，也可以有更多個欄位

舉例：

4.4.3 在 ALTER TABLE 時新增（複合）主鍵約束

欄位列表可以是一個欄位，也可以是多個欄位，如果是多個欄位的話，是複合主鍵

語法格式：

        1. ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 PRIMARY KEY;
        2. ALTER TABLE 表名稱 ADD PRIMARY KEY(欄位列表);

舉例：

4.5 刪除主鍵約束

刪除主鍵約束，不需要指定主鍵名，因為一個表只有一個主鍵，刪除主鍵約束後，非空還存在。（但在實際開發中，不會去刪除表中的主鍵約束）

語法格式：

        
    alter table 表名稱 
  
        drop primary key;

舉例：

5. 自增列：AUTO_INCREMENT

5.1 作用

某個欄位的值自增

5.2 關鍵字

auto_increment

5.3 特點和要求

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

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

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

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

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

5.4 新增自增約束

5.4.1 在CREATE TABLE 時新增自增約束

語法格式：

                create table 
    表名稱( 
  
                欄位名 資料型別 primary key auto_increment,
  
                欄位名 資料型別 ,
  
                欄位名 資料型別 ,
  
                欄位名 資料型別 
  
                );
  
                create table 表名稱( 
  
                欄位名 資料型別 ,
  
                欄位名 資料型別 unique key auto_increment, 
  
                欄位名 資料型別 
  
                );

舉例：

非法建立：

正確建立方式：

插入資料:

特殊情況(不推薦此寫法)：

5.4.2 在 ALTER TABLE 時新增自增約束

語法格式：

        alter table 
    表名稱 
  
        modify 欄位名 資料型別 auto_increment;

舉例：

5.5 刪除自增約束

語法格式：

        alter table 表名稱 modify 欄位名 資料型別;

舉例：

5.6 MySQL 8.0新特性—自增變數的持久化

在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 會根據重做紀錄檔中的資訊來初始化計數器的記憶體值。

6. FOREIGN KEY 約束

6.1 作用

限定某個表的某個欄位的參照完整性。

比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。

6.2 關鍵字

FOREIGN KEY

6.3 主表和從表/父表和子表

主表（父表）：被參照的表，被參考的表

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

例如：員工表的員工所在部門這個欄位的值要參考部門表：部門表是主表，員工表是從表。

例如：學生表、課程表、選課表：選課表的學生和課程要分別參考學生表和課程表，學生表和課程表是主表，選課表是從表。

6.4 特點

1. 從表的外來鍵列，必須參照/參考主表的主鍵或唯一約束的列

因為被依賴/被參考的值必須是唯一的

2. 在建立外來鍵約束時，如果不給外來鍵約束命名，預設名不是列名，而是自動產生一個外來鍵名（例如 student_ibfk_1;），也可以指定外來鍵約束名。

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

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

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

6. 在「從表」中指定外來鍵約束，並且一個表可以建立多個外來鍵約束

7. 從表的外來鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣，邏輯意義一致。如果型別不一樣，建立子表時，就會出現錯誤。

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

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

6.5 新增外來鍵約束

6.5.1 在 create table 時新增外來鍵約束

語法格式：

     
         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: 標示在父表中的列

(從表的某個欄位)的資料型別必須與主表名(被參考欄位)的資料型別一致，邏輯意義也一樣

(從表的某個欄位)的欄位名可以與主表名(被參考欄位)的欄位名一樣，也可以不一樣

舉例：

正確的建立方式：

錯誤的建立方式：

新增資料：

修改資料：

刪除資料：

6.5.2 在ALTER TABLE 時新增外來鍵約束

一般情況下，表與表的關聯都是提前設計好了的，因此，會在建立表的時候就把外來鍵約束定義好。不過，如果需要修改表的設計（比如新增新的欄位，增加新的關聯關係），但沒有預先定義外來鍵約束，那麼，就要用修改表的方式來補充定義。

語法格式：

        ALTER TABLE 從表名 
    
        ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的欄位) REFERENCES 主表名(被參照 欄位) [on update xx][on delete xx];

6.7 約束等級（級聯）

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.刪除資料

6.8 刪除外來鍵約束

流程如下：

(1)第一步先檢視約束名和刪除外來鍵約束

        #檢視某個表的約束名
    
        SELECT * FROM information_schema.table_constraints 
    
        WHERE table_name = '表名稱';
    
        ALTER TABLE 從表名 
    
        DROP FOREIGN KEY 外來鍵約束名;

（ 2）第二步檢視索引名和刪除索引。（注意，只能手動刪除）

#檢視某個表的索引名

        SHOW INDEX FROM 表名稱;      
        ALTER TABLE 從表名 DROP INDEX 索引名;

注意：刪除外來鍵約束後，必須手動刪除對應的索引

舉例：

6.9 開發場景

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

答：不是的

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

答：建外來鍵約束，你的操作（建立表、刪除表、新增、修改、刪除）會受到限制，從語法層面受到限制。

例如：在員工表中不可能新增一個員工資訊，它的部門的值在部門表中找不到。

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

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

答：沒有

拓展：

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

6.10 阿里開發規範

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

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

7. CHECK 約束

7.1 作用

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

7.2 關鍵字

CHECK

7.3 說明：MySQL 5.7 不支援

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

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

7.4 新增CHECK 約束

8. DEFAULT約束

8.1 作用

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

8.2 關鍵字

DEFAULT

8.3 新增預設值約束

8.3.1 在 CREATE TABLE時新增預設值約束

語法格式：

方式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(欄位名) 
    
                );

說明：預設值約束一般不在唯一鍵和主鍵列上加

舉例：

8.3.2 在 ALTER TABLE時新增預設值約束

語法格式：

        alter table 
    表名稱 modify 欄位名 資料型別 default 預設值; 
  
        alter table 表名稱 modify 欄位名 資料型別 default 預設值 not null
    ;

注：

1.如果這個欄位原來有非空約束，你還保留非空約束，那麼在加預設值約束時，還得保留非空約束，否則非空約束就被刪除了。

2.同理，在給某個欄位加非空約束也一樣，如果這個欄位原來有預設值約束，你想保留，也要在modify語句中保留預設值約束，否則就刪除了。

舉例：

8.4 刪除預設值約束

語法格式：

1.刪除預設值約束，也不保留非空約束

                alter table 
    表名稱 modify 欄位名 資料型別 ;

2.刪除預設值約束，保留非空約束

                alter table 
    表名稱 modify 欄位名 資料型別 not null
    ;

舉例：

9. 面試

面試 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約束知識點的詳細內容，更多請關注TW511.COM其它相關文章！