一文詳解MySQL表的約束
更新時間：2025年10月28日 09:19:43 作者：Albert Edison
本文聚焦數(shù)據(jù)庫表設計核心要素,涵蓋空屬性、默認值等基礎設置,詳解主鍵、自增長、唯一鍵、外鍵的定義與操作,通過商品、客戶、購買表案例,展現(xiàn)其實際應用邏輯,對mysql表約束相關知識感興趣的朋友跟隨小編一起看看吧
表的約束：表中一定要有各種約束，通過約束，讓我們未來插入數(shù)據(jù)庫表中的數(shù)據(jù)是符號預期的。
約束的本質(zhì)是通過技術手段，倒逼程序員，插入正確的數(shù)據(jù)。反過來，站在 MySQL 的視角，凡是插入進來的數(shù)據(jù)，都是符合數(shù)據(jù)約束的！
所以約束的最終目標：保證數(shù)據(jù)的完整性和可預期性。
真正約束字段的是數(shù)據(jù)類型，但是數(shù)據(jù)類型約束很單一，需要有一些額外的約束，更好的保證數(shù)據(jù)的合法性，從業(yè)務邏輯角度保證數(shù)據(jù)的正確性。比如有一個字段是 email，要求是唯一的。
表的約束很多，這里主要介紹如下幾個： null / not null、default、comment、zerofill、primary key、auto_increment、unique key。
1. 空屬性
兩個值：null（默認的）和 not null（不為空）。
數(shù)據(jù)庫默認字段基本都是字段為空，但是實際開發(fā)時，盡可能保證字段不為空，因為數(shù)據(jù)為空沒辦法參與運算。
mysql> select null; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select 1+null; +--------+ | 1+null | +--------+ | NULL | +--------+ 1 row in set (0.00 sec)
案例：創(chuàng)建一個班級表，包含班級名和班級所在的教室。
站在正常的業(yè)務邏輯中：
- 如果班級沒有名字，你不知道你在哪個班級。
- 如果教室名字可以為空，就不知道在哪上課。
所以我們在設計數(shù)據(jù)庫表的時候，一定要在表中進行限制，滿足上面條件的數(shù)據(jù)就不能插入到表中。這就是 “約束”。
先建表：
mysql> create table if not exists myclass(
-> class_name varchar(20) not null,
-> class_room varchar(20) not null,
-> other varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO | | NULL | |
| class_room | varchar(20) | NO | | NULL | |
| other | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)我們先做全列插入：
mysql> insert into myclass (class_name, class_room, other) values
-> ('高三1班', '101教室', '普通班');
Query OK, 1 row affected (0.00 sec)
mysql> select * from myclass;
+------------+------------+-----------+
| class_name | class_room | other |
+------------+------------+-----------+
| 高三1班 | 101教室 | 普通班 |
+------------+------------+-----------+
1 row in set (0.00 sec)
mysql> insert into myclass (class_name, class_room) values ('高三3班', '103教室');
Query OK, 1 row affected (0.01 sec)
mysql> select * from myclass;
+------------+------------+-----------+
| class_name | class_room | other |
+------------+------------+-----------+
| 高三1班 | 101教室 | 普通班 |
| 高三3班 | 103教室 | NULL |
+------------+------------+-----------+
2 rows in set (0.00 sec)結果如下：
繼續(xù)插入數(shù)據(jù)：
mysql> insert into myclass (class_name, class_room) values ('高三3班', '103教室');
Query OK, 1 row affected (0.01 sec)
mysql> select * from myclass;
+------------+------------+-----------+
| class_name | class_room | other |
+------------+------------+-----------+
| 高三1班 | 101教室 | 普通班 |
| 高三3班 | 103教室 | NULL |
+------------+------------+-----------+
2 rows in set (0.00 sec)結果如下：
如果沒有給教室數(shù)據(jù)，或者數(shù)據(jù)為空，則會插入失?。?/p>
mysql> insert into myclass (class_name) values ('高三4班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value
mysql> insert into myclass (class_name, class_room) values ('高三4班', NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null2. 默認值
默認值：某一種數(shù)據(jù)會經(jīng)常性的出現(xiàn)某個具體的值，可以在一開始就指定好，在需要真實數(shù)據(jù)的時候，用戶可以選擇性的使用默認值。
案例：建立一張人物信息表
mysql> create table if not exists t1(
-> name varchar(20) not null,
-> age tinyint unsigned default 18,
-> gender char(1) default '男'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 18 | |
| gender | char(1) | YES | | 男 | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)我們先做全列插入：
mysql> insert into t1 (name, age, gender) values ('小紅', 19, '女');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| 小紅 | 19 | 女 |
+--------+------+--------+
1 row in set (0.00 sec)結果如下：
然后做缺省插入：
mysql> insert into t1 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| 小紅 | 19 | 女 |
| 李四 | 18 | 男 |
+--------+------+--------+
2 rows in set (0.01 sec)可以看到，數(shù)據(jù)在插入的時候不給該字段賦值，就使用默認值。
我們重新建一張表，然后把 gender 字段，既設置了 not null，又設置了 default
mysql> create table if not exists t2(
-> name varchar(20) not null,
-> age tinyint default 18,
-> gender char(1) not null default '男'
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| age | tinyint(4) | YES | | 18 | |
| gender | char(1) | NO | | 男 | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)思考一下：如果不指定 gender 的值會怎么樣呢？
mysql> insert into t2 (name, age, gender) values ('小明', 20, '男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (name, age) values ('小紅', 22);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| 小紅 | 19 | 女 |
| 李四 | 18 | 男 |
+--------+------+--------+
2 rows in set (0.00 sec)結果如下，可以看到 default 和 not null 不沖突，而是互相補充的。
另外，只有設置了 default 的列，才可以在插入值的時候，對列進行省略
mysql> insert into t2 (name, age, gender) values ('李四', NULL, '男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (name, gender) values ('瑪麗', '女');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| 小明 | 20 | 男 |
| 小紅 | 22 | 男 |
| 李四 | NULL | 男 |
| 瑪麗 | 18 | 女 |
+--------+------+--------+
4 rows in set (0.00 sec)結果如下：
另外，如果我們建表不指定約束，那么 MySQL 會默認給我們添加 default null，也就是可以省略。
3. 列描述
列描述：comment，沒有實際含義，專門用來描述字段，會根據(jù)表創(chuàng)建語句保存，用來給程序員或 DBA 來進行了解。
還是先建一張表
mysql> create table if not exists t4(
-> name varchar(20) not null comment '姓名',
-> age tinyint unsigned default 18 comment '年齡',
-> gender char(1) default '男' comment '性別'
-> );
Query OK, 0 rows affected (0.01 sec)注意：not null 和 defalut 一般不需要同時出現(xiàn)，因為 default 本身有默認值，不會為空。
我們可以插入一行數(shù)據(jù)，來驗證看看：
mysql> insert into t4 (name) values ('小明');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t4;
+--------+------+--------+
| name | age | gender |
+--------+------+--------+
| 小明 | 18 | 男 |
+--------+------+--------+
1 row in set (0.00 sec)然后通過 desc 會發(fā)現(xiàn)查看不到注釋信息：
mysql> desc t4; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | 18 | | | gender | char(1) | YES | | 男 | | +--------+---------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
我們要通過 show 才可以看到：
4. zerofill
剛開始學習數(shù)據(jù)庫時，很多人對數(shù)字類型后面的長度很迷茫。我們先建一張表，通過 show 看看 t5 表的建表語句：
mysql> create table if not exists t5(
-> a int unsigned not null,
-> b int unsigned not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| a | int(10) unsigned | NO | | NULL | |
| b | int(10) unsigned | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table t5\G;
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`a` int(10) unsigned NOT NULL,
`b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)結果如下：
可以看到 int(10) 這個代表什么意思呢？整型不是 4 字節(jié)碼？這個 10 又代表什么呢？其實沒有 zerofill 這個屬性，括號內(nèi)的數(shù)字 10 是毫無意義的。
a 和 b 列就是前面插入的數(shù)據(jù)，如下：
mysql> insert into t5 (a, b) values (1, 2); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ 1 row in set (0.00 sec)
但是對列添加了 zerofill 屬性后，顯示的結果就有所不同了。修改 t5 表的屬性：
mysql> alter table t5 modify b int unsigned zerofill not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t5\G;
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`a` int(10) unsigned NOT NULL,
`b` int(10) unsigned zerofill NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)對 b 列添加了 zerofill 屬性，再進行查找，返回如下結果：
mysql> select * from t5; +---+------------+ | a | b | +---+------------+ | 1 | 0000000002 | +---+------------+ 1 row in set (0.00 sec)
我們插入一行數(shù)據(jù)也能看到結果：
這次可以看到 b 的值由原來的 2 變成 0000000002，這就是 zerofill 屬性的作用，如果寬度小于設定的寬度（這里設置的是 10），自動填充 0。
要注意的是，這只是最后顯示的結果，在 MySQL 中實際存儲的還是 2。為什么是這樣呢？我們可以用 hex 函數(shù)來證明。
mysql> select b, hex(b) from t5; +------------+--------+ | b | hex(b) | +------------+--------+ | 0000000002 | 2 | | 0000000200 | C8 | +------------+--------+ 2 rows in set (0.00 sec)
可以看出數(shù)據(jù)庫內(nèi)部存儲的還是 2，0000000002 只是設置了 zerofill 屬性后的一種格式化輸出而已。
5. 主鍵
主鍵：primary key 用來唯一的約束該字段里面的數(shù)據(jù)，不能重復，不能為空，一張表中最多只能有一個主鍵；主鍵所在的列通常是整數(shù)類型。
5.1 指定主鍵
創(chuàng)建表的時候直接在字段上指定主鍵。
mysql> create table if not exists test_key (
-> id int unsigned primary key comment '學號不能為空',
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)可以看到主鍵默認不能為空：
5.2 主鍵約束
主鍵對應的字段中不能重復，一旦重復，操作失敗。
mysql> insert into test_key values (1, '小明'); Query OK, 1 row affected (0.00 sec) mysql> select * from test_key; +----+--------+ | id | name | +----+--------+ | 1 | 小明 | +----+--------+ 1 row in set (0.00 sec) mysql> insert into test_key values (1, '小紅'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into test_key values (2, '小紅'); Query OK, 1 row affected (0.00 sec) mysql> select * from test_key; +----+--------+ | id | name | +----+--------+ | 1 | 小明 | | 2 | 小紅 | +----+--------+ 2 rows in set (0.00 sec)
另外，我們還可以根據(jù)主鍵來查找和修改：
mysql> select * from test_key where id=2; +----+--------+ | id | name | +----+--------+ | 2 | 小紅 | +----+--------+ 1 row in set (0.00 sec) mysql> update test_key set name='瑪麗' where id=2; Query OK, 1 row affected (0.30 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test_key; +----+--------+ | id | name | +----+--------+ | 1 | 小明 | | 2 | 瑪麗 | +----+--------+ 2 rows in set (0.00 sec)
5.3 刪除主鍵
我現(xiàn)在把 test_key 表的主鍵給刪除掉
mysql> alter table test_key drop primary key; Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc test_key; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
此時，可以插入 id 相同的值了：
5.4 追加主鍵
當表創(chuàng)建好以后，但是沒有主鍵的時候，可以再次追加主鍵。
我們可以給 test_key 的 id 重新添加主鍵，但是記得把表里面重復的記錄給刪除掉
mysql> alter table test_key add primary key(id); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test_key; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+
結果如下：
5.5 復合主鍵
在創(chuàng)建表的時候，在所有字段之后，使用 primary key（主鍵字段列表）來創(chuàng)建主鍵，如果有多個字段作為主鍵，可以使用復合主鍵。
也就是說，一個主鍵可以被添加到一列，或者多列上。
案例如下，我建一張課程表，然后把學號和課程號都設置主鍵
mysql> create table pick_course (
-> id int unsigned comment '學號',
-> course char(10) comment '課程代碼',
-> score tinyint unsigned comment '成績',
-> primary key(id, course)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc pick_course;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| course | char(10) | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)注意：有兩個 PRI 不代表有兩個主鍵，而是說明這兩個都是主鍵，也就是說，它倆合起來才算一個主鍵。
插入數(shù)據(jù)進行驗證：
mysql> insert into pick_course values(001, 085410, 90); Query OK, 1 row affected (0.00 sec) mysql> insert into pick_course values(002, 085410, 85); Query OK, 1 row affected (0.00 sec) mysql> insert into pick_course values(001, 085411, 95); Query OK, 1 row affected (0.00 sec) mysql> select * from pick_course; +----+--------+-------+ | id | course | score | +----+--------+-------+ | 1 | 85410 | 90 | | 1 | 85411 | 95 | | 2 | 85410 | 85 | +----+--------+-------+ 3 rows in set (0.00 sec) mysql> insert into pick_course values(001, 085410, 100); ERROR 1062 (23000): Duplicate entry '1-85410' for key 'PRIMARY' mysql> insert into pick_course values(002, 085410, 100); ERROR 1062 (23000): Duplicate entry '2-85410' for key 'PRIMARY'
可以看到 id 和 course 必須為不同的值，才可以插入數(shù)據(jù)：
6. 自增長
auto_increment：當對應的字段，不給值，會自動的被系統(tǒng)觸發(fā)，系統(tǒng)會從當前字段中已經(jīng)有的最大值 +1 操作，得到一個新的不同的值。通常和主鍵搭配使用，作為邏輯主鍵。
自增長的特點:
- 任何一個字段要做自增長，前提是本身是一個索引（key 一欄有值）。
- 自增長字段必須是整數(shù)。
- 一張表最多只能有一個自增長。
先創(chuàng)建一個表
mysql> create table if not exists t6 (
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t6;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)然后開始插入數(shù)據(jù)
mysql> insert into t6(name) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6(name) values ('b');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6(name) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)如果不指定 id 的話，是默認從 1 開始的，但是我們指定 id 為 1000 的話，那么下次 id 就會變?yōu)?1001：
另外，我們在建表的時候，還可以指定 auto_increment 的初始值：
mysql> create table t7 (
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null
-> )auto_increment=500;
Query OK, 0 rows affected (0.02 sec)
結果如下：
在插入后獲取上次插入的 AUTO_INCREMENT 的值（批量插入獲取的是第一個值）
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec)
6.1 索引
這里可以簡單的談一下索引。
在關系數(shù)據(jù)庫中，索引是一種單獨的、物理的對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。
索引的作用相當于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。索引提供指向存儲在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)您指定的排序順序?qū)@些指針排序。
數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的 SQL 語句執(zhí)行得更快，可快速訪問數(shù)據(jù)庫表中的特定信息。
7. 唯一鍵
一張表中有往往有很多字段需要唯一性，數(shù)據(jù)不能重復，但是一張表中只能有一個主鍵：唯一鍵就可以解決表中有多個字段需要唯一性約束的問題。
唯一鍵的本質(zhì)和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空字段不做唯一性比較。
我們先建立一張擁有唯一鍵的學生表
mysql> create table stu (
-> id char(20) unique comment '這是一個學生的唯一鍵',
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(20) | YES | UNI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)然后進行數(shù)據(jù)的插入：
mysql> insert into stu (id, name) values ('12345', '張三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+-------+--------+
| id | name |
+-------+--------+
| 12345 | 張三 |
+-------+--------+
1 row in set (0.00 sec)
mysql> insert into stu (id, name) values (NULL, '李四');
Query OK, 1 row affected (0.00 sec)結果如下：
我們再來建一張表：
mysql> create table student(
-> id char(20) primary key,
-> name varchar(32) not null,
-> telphone char(20) unique key,
-> qq varchar(64) unique key
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | char(20) | NO | PRI | NULL | |
| name | varchar(32) | NO | | NULL | |
| telphone | char(20) | YES | UNI | NULL | |
| qq | varchar(64) | YES | UNI | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)當我們設置了唯一鍵可以，可以避免一些未定義或者誤操作，從而保證插入表里面的數(shù)據(jù)不一樣：
關于唯一鍵和主鍵的區(qū)別：
- 我們可以簡單理解成，主鍵更多的是標識唯一性的。
- 而唯一鍵更多的是保證在業(yè)務上，不要和別的信息出現(xiàn)重復。
乍一聽好像沒啥區(qū)別，我們舉一個例子，假設一個場景：
- 比如在公司，我們需要一個員工管理系統(tǒng)，系統(tǒng)中有一個員工表，員工表中有兩列信息，一個身份證號碼，一個是員工工號，我們可以選擇身份號碼作為主鍵。
- 而我們設計員工工號的時候，需要一種約束：而所有的員工工號都不能重復。具體指的是在公司的業(yè)務上不能重復，我們設計表的時候，需要這個約束，那么就可以將員工工號設計成為唯一鍵。
- 一般而言，我們建議將主鍵設計成為和當前業(yè)務無關的字段，這樣，當業(yè)務調(diào)整的時候，我們可以盡量不會對主鍵做過大的調(diào)整。
8. 外鍵
外鍵用于定義主表和從表之間的關系：外鍵約束主要定義在從表上，主表則必須是有主鍵約束或 unique 約束。當定義外鍵后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或為 null。
語法：
foreign key (字段名) references 主表(列)
案例如下圖所示：
對上面的示意圖進行設計：
- 先創(chuàng)建主鍵表
mysql> create table if not exists class (
-> id int primary key,
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)- 再創(chuàng)建從表
指明我們的外鍵列為 class_id，并且將外鍵這一列和我們的主表當中的 id 列建立我們的外鍵約束關系
mysql> create table if not exists student (
-> id int unsigned primary key,
-> name varchar(20) not null,
-> telphone varchar(32) unique key,
-> class_id int,
-> foreign key(class_id) references class(id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| telphone | varchar(32) | YES | UNI | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)- 正常插入數(shù)據(jù)
先在對 class 表插入數(shù)據(jù)
mysql> desc class; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into class values (1, '人工智能101'), (2, '人工智能102'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from class; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 人工智能101 | | 2 | 人工智能102 | +----+-----------------+ 2 rows in set (0.00 sec)
然后再對學生表插入數(shù)據(jù)
mysql> insert into student (id, name, telphone, class_id) values (212001, '張三', '12345', 1); Query OK, 1 row affected (0.01 sec) mysql> insert into student (id, name, telphone, class_id) values (212002, '李四', '12346', 2); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +--------+--------+----------+----------+ | id | name | telphone | class_id | +--------+--------+----------+----------+ | 212001 | 張三 | 12345 | 1 | | 212002 | 李四 | 12346 | 2 | +--------+--------+----------+----------+ 2 rows in set (0.00 sec)
- 插入一個班級號為 3 的學生，因為沒有這個班級，所以插入不成功
mysql> insert into student (id, name, telphone, class_id) values (212003, '王五', '12347', 3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
- 假設我現(xiàn)在要刪除 人工智能101班，但是因為班里面有學生，所以無法刪除，這就是外鍵約束
mysql> select * from student; +--------+--------+----------+----------+ | id | name | telphone | class_id | +--------+--------+----------+----------+ | 212001 | 張三 | 12345 | 1 | | 212002 | 李四 | 12346 | 2 | +--------+--------+----------+----------+ 2 rows in set (0.00 sec) mysql> select * from class; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 人工智能101 | | 2 | 人工智能102 | +----+-----------------+ 2 rows in set (0.00 sec) mysql> delete from class where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
我們只有把班級里面的學生刪除，然后刪除班級
mysql> delete from student where id=212001; Query OK, 1 row affected (0.00 sec) mysql> select * from student; +--------+--------+----------+----------+ | id | name | telphone | class_id | +--------+--------+----------+----------+ | 212002 | 李四 | 12346 | 2 | +--------+--------+----------+----------+ 1 row in set (0.00 sec) mysql> delete from class where id=1; Query OK, 1 row affected (0.01 sec) mysql> select * from class; +----+-----------------+ | id | name | +----+-----------------+ | 2 | 人工智能102 | +----+-----------------+ 1 row in set (0.00 sec)
- 插入班級 id 為 null，比如來了一個學生，目前還沒有分配班級
mysql> insert into student values(212003, '趙六', '12349', null); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +--------+--------+----------+----------+ | id | name | telphone | class_id | +--------+--------+----------+----------+ | 212002 | 李四 | 12346 | 2 | | 212003 | 趙六 | 12349 | NULL | +--------+--------+----------+----------+ 2 rows in set (0.00 sec)
所以外鍵的本質(zhì)：
- 1、產(chǎn)生關聯(lián)。
- 2、增加約束，來整體保證表跟表之間的完整性。
8.1 如何理解外鍵約束
首先我們承認，這個世界是數(shù)據(jù)很多都是相關性的。
理論上，上面的例子，我們不創(chuàng)建外鍵約束，就正常建立學生表，以及班級表，該有的字段我們都有。
此時，在實際使用的時候，可能會出現(xiàn)什么問題？
有沒有可能插入的學生信息中有具體的班級，但是該班級卻沒有在班級表中？
比如計算機學院的人工智能專業(yè)只開了【人工智能 1 班】和【人工智能 2 班】，但是在上課的學生里面竟然有【人工智能 3 班】的學生（這個班目前并不存在），這很明顯是有問題的。
因為此時兩張表在業(yè)務上是有相關性的，但是在業(yè)務上沒有建立約束關系，那么就可能出現(xiàn)問題。
解決方案就是通過外鍵完成的。建立外鍵的本質(zhì)其實就是把相關性交給 MySQL 去審核了，提前告訴 MySQL 表之間的約束關系，那么當用戶插入不符合業(yè)務邏輯的數(shù)據(jù)的時候，MySQL 不允許你插入。
9. 綜合案例
有一個商店的數(shù)據(jù)，記錄客戶及購物情況，有以下三個表組成：
1?? 商品表 goods：
- 商品編號 goods_id
- 商品名 goods_name
- 單價 unitprice
- 商品類別 category
- 供應商 provider
2?? 客戶表 customer：
- 客戶號 customer_id
- 姓名 name
- 住址 address
- 郵箱 email
- 性別 sex
- 身份證 card_id
3?? 購買表 purchase：
- 購買訂單號 order_id
- 客戶號 customer_id
- 商品號 goods_id
- 購買數(shù)量 nums
要求：
- 每個表的主外鍵
- 客戶的姓名不能為空值
- 郵箱不能重復
- 客戶的性別（男，女）
先創(chuàng)建一個數(shù)據(jù)庫：
mysql> create database if not exists store; Query OK, 1 row affected (0.00 sec) mysql> use store; Database changed
9.1 創(chuàng)建商品表
mysql> create table if not exists goods (
-> goods_id int primary key auto_increment comment '商品編號',
-> goods_name varchar(32) not null comment '商品名稱',
-> unitprice int not null default 0 comment '單價，單位分',
-> category varchar(12) comment '商品分類',
-> provider varchar(64) not null comment '供應商名稱'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc goods;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(32) | NO | | NULL | |
| unitprice | int(11) | NO | | 0 | |
| category | varchar(12) | YES | | NULL | |
| provider | varchar(64) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)注意：
- 主鍵（Primary Key）：goods_id
- 外鍵（Foreign Key）：無（此表是被引用方）。
9.2 創(chuàng)建客戶表
mysql> 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 '身份證'
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc customer;
+-------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+---------+----------------+
| customer_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| address | varchar(256) | YES | | NULL | |
| email | varchar(64) | YES | UNI | NULL | |
| sex | enum('男','女') | NO | | NULL | |
| card_id | char(18) | YES | UNI | NULL | |
+-------------+-------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)注意：
- 主鍵（Primary Key）：customer_id
- 外鍵（Foreign Key）：無（此表是被引用方）。
- 唯一約束（Unique Key）：email、card_id。
9.3 創(chuàng)建購買表
mysql> 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 0 comment '購買數(shù)量',
-> foreign key (customer_id) references customer(customer_id),
-> foreign key (goods_id) references goods(goods_id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc purchase;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| order_id | int(11) | NO | PRI | NULL | auto_increment |
| customer_id | int(11) | YES | MUL | NULL | |
| goods_id | int(11) | YES | MUL | NULL | |
| nums | int(11) | YES | | 0 | |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)注意：
- 主鍵（Primary Key）：order_id
- 外鍵（Foreign Key）：
- customer_id → 關聯(lián)到 customer.customer_id，表示 “誰買的”。
- goods_id → 關聯(lián)到 goods.goods_id，表示 “買的什么”。
9.4 主鍵和外鍵
| 表名 | 主鍵 | 外鍵 | 外鍵關聯(lián) |
|---|---|---|---|
| goods | goods_id | — | — |
| customer | customer_id | — | — |
| purchase | order_id | customer_id, goods_id | customer(customer_id), goods(goods_id) |
關系圖示：
customer (客戶表)
│
│ customer_id ←───┐
│ │
└──────────────────? purchase (購買表) ───? goods (商品表)
▲ │
│ goods_id │
└──────────────────────┘注意：
- 一個客戶（customer）可以有多筆訂單（purchase）。
- 一個商品（goods）可以被多筆訂單購買。
- 所以這是一個 多對多關系，中間的 purchase 表是 關聯(lián)表。
9.5 插入數(shù)據(jù)
插入商品表 goods
mysql> insert into goods (goods_name, unitprice, category, provider) values
-> ('蘋果', 500, '水果', '青島果業(yè)有限公司'),
-> ('香蕉', 300, '水果', '海南香蕉集團'),
-> ('牛奶', 450, '飲品', '伊利實業(yè)股份有限公司'),
-> ('洗發(fā)水', 1200, '日用品', '寶潔公司'),
-> ('牙膏', 800, '日用品', '高露潔公司');
Query OK, 5 rows affected (0.00 sec)
mysql> select * from goods;
+----------+------------+-----------+-----------+--------------------------------+
| goods_id | goods_name | unitprice | category | provider |
+----------+------------+-----------+-----------+--------------------------------+
| 1 | 蘋果 | 500 | 水果 | 青島果業(yè)有限公司 |
| 2 | 香蕉 | 300 | 水果 | 海南香蕉集團 |
| 3 | 牛奶 | 450 | 飲品 | 伊利實業(yè)股份有限公司 |
| 4 | 洗發(fā)水 | 1200 | 日用品 | 寶潔公司 |
| 5 | 牙膏 | 800 | 日用品 | 高露潔公司 |
+----------+------------+-----------+-----------+--------------------------------+
5 rows in set (0.00 sec)插入客戶表 customer
mysql> insert into customer (name, address, email, sex, card_id) values
-> ('張三', '北京市海淀區(qū)中關村1號', 'zhangsan@example.com', '男', '110101199001010011'),
-> ('李四', '上海市浦東新區(qū)世紀大道100號', 'lisi@example.com', '女', '310101199212123456'),
-> ('王五', '廣州市天河區(qū)花城大道88號', 'wangwu@example.com', '男', '440101198805056789');
Query OK, 3 rows affected (0.00 sec)
mysql> select * from customer;
+-------------+--------+-----------------------------------------+----------------------+-----+--------------------+
| customer_id | name | address | email | sex | card_id |
+-------------+--------+-----------------------------------------+----------------------+-----+--------------------+
| 1 | 張三 | 北京市海淀區(qū)中關村1號 | zhangsan@example.com | 男 | 110101199001010011 |
| 2 | 李四 | 上海市浦東新區(qū)世紀大道100號 | lisi@example.com | 女 | 310101199212123456 |
| 3 | 王五 | 廣州市天河區(qū)花城大道88號 | wangwu@example.com | 男 | 440101198805056789 |
+-------------+--------+-----------------------------------------+----------------------+-----+--------------------+
3 rows in set (0.00 sec)插入購買表 purchase（需要使用上面插入的主鍵）
mysql> insert into purchase (customer_id, goods_id, nums) values
-> (1, 1, 5), -- 張三 買了 5 個 蘋果
-> (1, 3, 2), -- 張三 買了 2 盒 牛奶
-> (2, 2, 10), -- 李四 買了 10 根 香蕉
-> (2, 4, 1), -- 李四 買了 1 瓶 洗發(fā)水
-> (3, 5, 3); -- 王五 買了 3 支 牙膏
Query OK, 5 rows affected (0.00 sec)
mysql> select * from purchase;
+----------+-------------+----------+------+
| order_id | customer_id | goods_id | nums |
+----------+-------------+----------+------+
| 1 | 1 | 1 | 5 |
| 2 | 1 | 3 | 2 |
| 3 | 2 | 2 | 10 |
| 4 | 2 | 4 | 1 |
| 5 | 3 | 5 | 3 |
+----------+-------------+----------+------+
5 rows in set (0.00 sec)9.6 驗證外鍵和關聯(lián)查詢
執(zhí)行以下 SQL 語句看看結果是否正確：
-- 查詢所有訂單及關聯(lián)信息
mysql> select
-> p.order_id,
-> c.name as customer_name,
-> g.goods_name,
-> p.nums,
-> g.unitprice,
-> g.unitprice * p.nums as total_price
-> from purchase p
-> join customer c on p.customer_id = c.customer_id
-> join goods g on p.goods_id = g.goods_id;
查詢結果如下：
到此這篇關于MySQL表的約束的文章就介紹到這了,更多相關mysql表的約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql8.018在linux上安裝與配置過程，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2020-01-01
MySQL中的LOCATE和POSITION函數(shù)使用方法
不常用：MySQL中的LOCATE和POSITION函數(shù)2010-02-02
如何在Java程序中訪問mysql數(shù)據(jù)庫中的數(shù)據(jù)并進行簡單的操作
這篇文章主要介紹了如何在Java程序中訪問mysql數(shù)據(jù)庫中的數(shù)據(jù)并進行簡單的操作的相關資料,需要的朋友可以參考下2016-05-05
本文主要介紹了mysql從零單排之快照讀與當前讀的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2026-04-04
從MySQL的源碼剖析Innodb buffer的命中率計算
這篇文章主要介紹了從MySQL的源碼剖析Innodb buffer的命中率計算,作者結合C語言寫的算法來分析innodb buffer hit Ratios,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了mysql8.0.20下載安裝及遇到的問題,本文通過圖文并茂的形式給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2020-05-05
這篇文章主要為大家詳細介紹了mysql 8.0.17 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-08-08
使用MySQL Administrator 登錄，報錯： Either the server service or the configuration file could not be found.Startup variable and service section are there for disabled.2010-12-12
linux下指定mysql數(shù)據(jù)庫服務器主從同步的配置實例
linux下指定數(shù)據(jù)庫服務器主從同步的配置實例，有需要的朋友可以參考下2013-01-01
自用mysql自帶命令實現(xiàn)數(shù)據(jù)庫備份還原的方法
本文章介紹了都是mysql常用的命令一些數(shù)據(jù)導入導出的命令了，只要我們撐握這些命令就可以方法快速的給我們的數(shù)據(jù)庫進行備份還原了2012-04-04