MySQL 表約束從基礎約束到外鍵關聯(lián)實戰(zhàn)案例詳解

更新時間：2026年04月09日 10:49:52 作者：草莓熊Lotso

文章全面介紹了MySQL的核心表約束,結合PPT實戰(zhàn)案例講解用法、區(qū)別與避坑點,幫你設計出健壯的數據庫表結構,通過實例展示了如何設計健壯的數據庫表結構,并提供了約束選型和避坑指南,感興趣的朋友跟隨小編一起看看吧

前言：

在 MySQL 數據庫設計中，數據類型定義了字段的存儲格式，而表約束則從業(yè)務邏輯層面保證數據的合法性和完整性。沒有約束的表可能出現(xiàn)空值、重復數據、邏輯沖突等問題（如學生所屬班級不存在），而合理使用約束能讓數據庫 “自我校驗”，減少程序中的數據校驗邏輯。本文將全面拆解 MySQL 核心表約束，結合 PPT 實戰(zhàn)案例講解用法、區(qū)別與避坑點，幫你設計出健壯的數據庫表結構。

一. 表約束核心概念

表約束是對表中字段的規(guī)則限制，用于保證數據的準確性、唯一性和關聯(lián)性。MySQL 支持的核心約束包括：

空屬性約束（NULL/NOT NULL）：限制字段是否允許為空；
默認值約束（DEFAULT）：字段未賦值時自動使用默認值；
列描述（COMMENT）：字段說明（無校驗作用）；
零填充約束（ZEROFILL）：數字類型不足指定寬度時填充 0；
主鍵約束（PRIMARY KEY）：唯一標識記錄，非空且唯一；
自增長約束（AUTO_INCREMENT）：整數字段自動遞增；
唯一鍵約束（UNIQUE KEY）：字段值唯一，允許為空；
外鍵約束（FOREIGN KEY）：關聯(lián)兩張表，保證數據邏輯一致性。

約束類型	描述
空屬性約束（NULL/NOT NULL）	限制字段是否允許為空值。
默認值約束（DEFAULT）	字段未賦值時自動使用默認值。
列描述（COMMENT）	用于字段說明，沒有校驗作用。
零填充約束（ZEROFILL）	數字類型不足指定寬度時在前面填充零。
主鍵約束（PRIMARY KEY）	唯一標識表中的每一行記錄，字段值非空且唯一。
自增長約束（AUTO_INCREMENT）	整數字段在插入新記錄時自動遞增。
唯一鍵約束（UNIQUE KEY）	保證字段值唯一，但允許為空值（通常只允許一個空值）。
外鍵約束（FOREIGN KEY）	用于關聯(lián)兩張表，保證數據的一致性和完整性。

二. 基礎約束：NULL/NOT NULL 與 DEFAULT

基礎約束主要控制字段的空值和默認值，是表設計的基礎要求。

2.1 空屬性約束（NULL/NOT NULL）

NULL：默認值，字段允許為空（空值無法參與運算，如1+NULL=NULL）；
NOT NULL：字段不允許為空，插入 / 更新時必須賦值。

實戰(zhàn)案例：
創(chuàng)建班級表，要求班級名和教室不能為空：

-- 創(chuàng)建表（班級名和教室非空）
CREATE TABLE myclass(
  class_name VARCHAR(20) NOT NULL,
  class_room VARCHAR(10) NOT NULL
);
-- 插入合法數據（成功）
INSERT INTO myclass VALUES('class1', '301');
-- 插入非法數據（未給class_room賦值，報錯）
INSERT INTO myclass(class_name) VALUES('class2');
-- 報錯：ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

2.2 默認值約束（DEFAULT）

當字段經常性出現(xiàn)某個固定值時，可設置DEFAULT，插入時省略該字段則自動使用默認值。
實戰(zhàn)案例：
創(chuàng)建用戶表，年齡默認 0，性別默認 “男”：

CREATE TABLE tt10(
  name VARCHAR(20) NOT NULL, -- 非空（必須賦值）
  age TINYINT UNSIGNED DEFAULT 0, -- 默認0
  sex CHAR(2) DEFAULT '男' -- 默認男
);
-- 插入時僅賦值name，age和sex使用默認值
INSERT INTO tt10(name) VALUES('zhangsan');
-- 查詢結果
SELECT * FROM tt10;
-- +----------+-----+-----+
-- | name     | age | sex |
-- +----------+-----+-----+
-- | zhangsan |   0 | 男  |
-- +----------+-----+-----+

注意：NOT NULL和DEFAULT一般不同時使用（DEFAULT已保證字段非空）。

2.3 列描述（COMMENT）

COMMENT用于描述字段含義，無實際校驗作用，僅方便開發(fā)者和 DBA 理解表結構，需通過SHOW CREATE TABLE查看。

CREATE TABLE tt12(
  name VARCHAR(20) NOT NULL COMMENT '姓名',
  age TINYINT UNSIGNED DEFAULT 0 COMMENT '年齡',
  sex CHAR(2) DEFAULT '男' COMMENT '性別'
);
-- desc無法查看注釋
DESC tt12;
-- 通過SHOW CREATE TABLE查看注釋
SHOW CREATE TABLE tt12\G
-- 結果：
-- `name` varchar(20) NOT NULL COMMENT '姓名',
-- `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年齡',
-- `sex` char(2) DEFAULT '男' COMMENT '性別'

2.4 零填充約束（ZEROFILL）

僅對數字類型有效，當字段值的寬度小于設定寬度時，自動在左側填充 0（僅格式化顯示，實際存儲值不變）。
實戰(zhàn)案例：

-- 創(chuàng)建表，a字段設置zerofill
CREATE TABLE tt3(
  a INT(5) UNSIGNED ZEROFILL, -- 寬度5，零填充
  b INT(10) UNSIGNED
);
-- 插入數據
INSERT INTO tt3 VALUES(1, 2);
-- 查詢結果（a字段填充為00001）
SELECT * FROM tt3;
-- +-------+------+
-- | a     | b    |
-- +-------+------+
-- | 00001 |    2 |
-- +-------+------+
-- 驗證實際存儲值（仍為1）
SELECT a, HEX(a) FROM tt3;
-- +-------+-------+
-- | a     | HEX(a) |
-- +-------+-------+
-- | 00001 | 1     |
-- +-------+-------+

關鍵：無ZEROFILL時，數字類型后的寬度（如INT(10)）毫無意義，僅用于顯示格式化。

三. 核心約束：主鍵、自增長與唯一鍵

主鍵、自增長、唯一鍵是保證數據唯一性的核心約束，解決 “重復數據” 和 “邏輯主鍵” 問題。

3.1 主鍵約束（PRIMARY KEY）

主鍵是表的 “唯一標識”，核心特性：

非空（NOT NULL）且唯一（UNIQUE）；
一張表最多只能有一個主鍵；
通常用于標識唯一記錄（如用戶 ID、學號）。

實戰(zhàn)案例 1：單字段主鍵

-- 創(chuàng)建表時指定主鍵
CREATE TABLE tt13(
  id INT UNSIGNED PRIMARY KEY COMMENT '學號（主鍵）',
  name VARCHAR(20) NOT NULL
);
-- 插入合法數據（成功）
INSERT INTO tt13 VALUES(1, 'aaa');
-- 插入重復主鍵（報錯）
INSERT INTO tt13 VALUES(1, 'bbb');
-- 報錯：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

實戰(zhàn)案例 2：復合主鍵（多字段聯(lián)合主鍵）
當單個字段無法唯一標識記錄時，可使用復合主鍵（多字段聯(lián)合唯一）：

-- 學生成績表：id（學號）+ course（課程代碼）為復合主鍵
CREATE TABLE tt14(
  id INT UNSIGNED,
  course CHAR(10) COMMENT '課程代碼',
  score TINYINT UNSIGNED DEFAULT 60 COMMENT '成績',
  PRIMARY KEY(id, course) -- 復合主鍵
);
-- 插入合法數據（成功）
INSERT INTO tt14(id, course) VALUES(1, '123');
-- 插入重復復合主鍵（報錯）
INSERT INTO tt14(id, course) VALUES(1, '123');
-- 報錯：ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY'

主鍵的添加與刪除

-- 給已有表添加主鍵
ALTER TABLE tt13 ADD PRIMARY KEY(id);
-- 刪除主鍵（注意：若主鍵關聯(lián)自增長，需先取消自增長）
ALTER TABLE tt13 DROP PRIMARY KEY;

3.2 自增長約束（AUTO_INCREMENT）

自增長字段會 自動從當前最大值 + 1 生成新值，核心特性：

必須是整數類型；
必須是索引（KEY一欄有值，通常與主鍵搭配）；
一張表最多只能有一個自增長。

實戰(zhàn)案例：

-- 主鍵+自增長（邏輯主鍵）
CREATE TABLE tt21(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL DEFAULT ''
);
-- 插入時省略id，自動遞增
INSERT INTO tt21(name) VALUES('a');
INSERT INTO tt21(name) VALUES('b');
-- 查詢結果
SELECT * FROM tt21;
-- +----+------+
-- | id | name |
-- +----+------+
-- |  1 | a    |
-- |  2 | b    |
-- +----+------+
-- 獲取上次插入的自增長值
SELECT LAST_INSERT_ID(); -- 結果：1（批量插入返回第一個值）

3.3 唯一鍵約束（UNIQUE KEY）

唯一鍵用于保證字段值唯一，但允許為空（空值不參與唯一性比較），解決 “一張表多個唯一字段” 的需求（主鍵僅能有一個）。

主鍵與唯一鍵的區(qū)別：
以下是生成的表格：

特性	主鍵（PRIMARY KEY）	唯一鍵（UNIQUE KEY）
唯一性	是	是
非空性	是	否（允許空值）
一張表數量	最多 1 個	多個
核心作用	標識唯一記錄	保證業(yè)務字段不重復

實戰(zhàn)案例：
創(chuàng)建學生表，學號唯一（允許為空）：

CREATE TABLE student(
  id CHAR(10) UNIQUE COMMENT '學號（唯一，可空）',
  name VARCHAR(10)
);
-- 插入合法數據（成功）
INSERT INTO student(id, name) VALUES('01', 'aaa');
INSERT INTO student(id, name) VALUES(NULL, 'bbb'); -- 空值允許
-- 插入重復唯一鍵（報錯）
INSERT INTO student(id, name) VALUES('01', 'ccc');
-- 報錯：ERROR 1062 (23000): Duplicate entry '01' for key 'id'

四. 關聯(lián)約束：外鍵（FOREIGN KEY）

外鍵用于定義主表和從表的關聯(lián)關系，保證數據的邏輯一致性（如學生的班級必須存在于班級表中）。

4.1 外鍵核心規(guī)則

外鍵定義在從表上，主表必須有主鍵或唯一鍵；
從表外鍵字段的值必須在主表對應字段中存在，或為NULL；
主表刪除 / 修改關聯(lián)記錄時，需處理從表關聯(lián)數據（如級聯(lián)刪除、拒絕操作）。

4.2 實戰(zhàn)案例

創(chuàng)建班級表（主表）和學生表（從表），學生表的class_id關聯(lián)班級表的id：

-- 1. 創(chuàng)建主表（班級表）
CREATE TABLE myclass(
  id INT PRIMARY KEY, -- 主鍵
  name VARCHAR(30) NOT NULL COMMENT '班級名'
);
-- 2. 創(chuàng)建從表（學生表），添加外鍵
CREATE TABLE stu(
  id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL COMMENT '學生名',
  class_id INT,
  -- 外鍵：class_id關聯(lián)myclass的id
  FOREIGN KEY (class_id) REFERENCES myclass(id)
);
-- 3. 插入主表數據
INSERT INTO myclass VALUES(10, 'C++大牛班'), (20, 'Java大神班');
-- 4. 插入合法從表數據（class_id在主表存在）
INSERT INTO stu VALUES(100, '張三', 10), (101, '李四', 20);
-- 5. 插入非法從表數據（class_id=30在主表不存在，報錯）
INSERT INTO stu VALUES(102, '王五', 30);
-- 報錯：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
-- 6. 插入class_id=NULL（未分配班級，成功）
INSERT INTO stu VALUES(102, '趙六', NULL);

4.3 外鍵的意義

外鍵的核心價值是 “讓數據庫自動校驗數據關聯(lián)性”，避免出現(xiàn)邏輯沖突（如不存在的班級、不存在的用戶訂單）。若不設置外鍵，需在程序中手動校驗，增加開發(fā)成本且易出錯。

五. 綜合實戰(zhàn)：設計電商訂單表

結合所有約束，設計電商系統(tǒng)的商品表、客戶表、購買表，滿足以下需求：

商品表：商品編號自增主鍵，名稱非空，單價默認 0；
客戶表：客戶編號自增主鍵，姓名非空，郵箱唯一，性別枚舉（男 / 女），身份證唯一；
購買表：訂單號自增主鍵，關聯(lián)客戶編號和商品編號（外鍵），購買數量默認 0。

-- 創(chuàng)建數據庫
CREATE DATABASE IF NOT EXISTS bit32mall DEFAULT CHARACTER SET utf8;
USE bit32mall;
-- 1. 商品表（主表）
CREATE TABLE IF NOT EXISTS goods(
  goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品編號',
  goods_name VARCHAR(32) NOT NULL COMMENT '商品名稱',
  unitprice FLOAT NOT NULL DEFAULT 0.1 COMMENT '單價（單位：分）',
  category VARCHAR(12) COMMENT '商品分類', // 也可以用枚舉
  provider VARCHAR(64) NOT NULL COMMENT '供應商名稱' // 也可以用枚舉
);
-- 2. 客戶表（主表）
CREATE TABLE IF NOT EXISTS customer(
  customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '客戶編號',
  name VARCHAR(32) NOT NULL COMMENT '客戶姓名',
  address VARCHAR(256) COMMENT '客戶地址',
  email VARCHAR(64) UNIQUE KEY COMMENT '電子郵箱（唯一）',
  sex ENUM('男','女') NOT NULL COMMENT '性別',
  card_id CHAR(18) UNIQUE KEY COMMENT '身份證（唯一）'
);
-- 3. 購買表（從表，關聯(lián)客戶表和商品表）
CREATE TABLE IF NOT EXISTS purchase(
  order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '訂單號',
  customer_id INT COMMENT '客戶編號',
  goods_id INT COMMENT '商品編號',
  nums INT DEFAULT 1 COMMENT '購買數量',
  -- 外鍵關聯(lián)
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
  FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

六. 約束選型避坑指南和總結

優(yōu)先使用非空約束：盡量讓字段NOT NULL，空值會導致查詢條件（如WHERE age=0）失效，且無法參與運算；
主鍵選擇邏輯 ID：主鍵建議用與業(yè)務無關的自增整數（如id），避免用身份證、手機號等業(yè)務字段（需頻繁修改）；
唯一鍵保證業(yè)務唯一性：如郵箱、身份證等業(yè)務字段，用UNIQUE KEY約束，而非主鍵；
外鍵謹慎使用：外鍵會降低表的插入 / 更新性能，高并發(fā)場景可去掉外鍵，在程序中校驗關聯(lián)性；
自增長字段注意重置：刪除表中數據后，自增長值不會自動重置，需用ALTER TABLE 表名 AUTO_INCREMENT=1手動重置。

總結：
MySQL 表約束是數據完整性的核心保障，從基礎的空值 / 默認值約束，到核心的主鍵 / 唯一鍵約束，再到關聯(lián)的外鍵約束，各自解決不同場景的問題：

基礎約束（NULL/DEFAULT/COMMENT/ZEROFILL）：控制字段基礎屬性；
核心約束（PRIMARY KEY/AUTO_INCREMENT/UNIQUE KEY）：保證數據唯一性；
關聯(lián)約束（FOREIGN KEY）：保證表間數據邏輯一致。

到此這篇關于MySQL 表約束從基礎約束到外鍵關聯(lián)實戰(zhàn)案例詳解的文章就介紹到這了,更多相關mysql表約束內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

詳解Mysql函數調用優(yōu)化
這篇文章主要介紹了Mysql 函數調用優(yōu)化的相關資料，幫助大家更好的理解和學習使用MySQL數據庫，感興趣的朋友可以了解下
2021-04-04
高版本Mysql使用group?by分組報錯的解決方案
GROUP?BY?語句用于結合合計函數,根據一個或多個列對結果集進行分組,下面這篇文章主要給大家介紹了關于高版本Mysql使用group?by分組報錯的解決方案,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2023-03-03
Mysql?exists用法小結
這篇文章主要介紹了Mysql?exists用法的的相關資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下
2020-12-12
mysql函數日期和時間函數匯總
這篇文章主要介紹了mysql函數日期和時間函數匯總，日期和時間函數主要用來處理日期和時間值，一般的日期函數除了使用??date???類型的參數外，也可以使用??datetime???或者??timestamp??類型的參數，但會忽略這些值的時間部分
2022-07-07
MySQL使用innobackupex備份連接服務器失敗的解決方法
這篇文章主要為大家詳細介紹了MySQL使用innobackupex備份連接服務器失敗的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-02-02
Mysql之DELETE操作對應的undo日志方式
InnoDB通過正常記錄鏈表和垃圾鏈表管理數據頁,刪除記錄先標記為deletemark,后由purge線程移入垃圾鏈表回收空間,PAGE_FREE指向垃圾鏈表頭,PAGE_GARBAGE記錄可重用空間大小,Undo日志保存trx_id和roll_pointer,用于版本控制和回滾
2025-08-08
mysql 8.0.11 安裝步驟詳解
這篇文章主要為大家詳細介紹了mysql 8.0.11 安裝步驟，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2018-08-08
MySQL學習（七）：Innodb存儲引擎索引的實現(xiàn)原理詳解
這篇文章主要介紹了Innodb存儲引擎索引的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2019-04-04
Mysql中被鎖住的表查詢以及如何解鎖詳解
這篇文章主要介紹了Mysql中被鎖住的表查詢以及如何解鎖的相關資料,這些方法可以幫助你釋放鎖并恢復表的正常使用,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2025-03-03
Mysql清空表數據庫命令truncate和delete詳解
這篇文章主要介紹了Mysql數據庫清空表truncate和delete的相關知識，本文給大家講解的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
2023-06-06