一文詳解MySQL表的約束
更新時間：2025年10月28日 09:19:43 作者：Albert Edison
本文聚焦數(shù)據(jù)庫表設(shè)計核心要素,涵蓋空屬性、默認值等基礎(chǔ)設(shè)置,詳解主鍵、自增長、唯一鍵、外鍵的定義與操作,通過商品、客戶、購買表案例,展現(xiàn)其實際應(yīng)用邏輯,對mysql表約束相關(guān)知識感興趣的朋友跟隨小編一起看看吧
表的約束：表中一定要有各種約束，通過約束，讓我們未來插入數(shù)據(jù)庫表中的數(shù)據(jù)是符號預(yù)期的。
約束的本質(zhì)是通過技術(shù)手段，倒逼程序員，插入正確的數(shù)據(jù)。反過來，站在 MySQL 的視角，凡是插入進來的數(shù)據(jù)，都是符合數(shù)據(jù)約束的！
所以約束的最終目標：保證數(shù)據(jù)的完整性和可預(yù)期性。
真正約束字段的是數(shù)據(jù)類型，但是數(shù)據(jù)類型約束很單一，需要有一些額外的約束，更好的保證數(shù)據(jù)的合法性，從業(yè)務(wù)邏輯角度保證數(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è)務(wù)邏輯中：
- 如果班級沒有名字，你不知道你在哪個班級。
- 如果教室名字可以為空，就不知道在哪上課。
所以我們在設(shè)計數(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)結(jié)果如下：
繼續(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)結(jié)果如下：
如果沒有給教室數(shù)據(jù)，或者數(shù)據(jù)為空，則會插入失敗：
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)結(jié)果如下：
然后做缺省插入：
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 字段，既設(shè)置了 not null，又設(shè)置了 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)結(jié)果如下，可以看到 default 和 not null 不沖突，而是互相補充的。
另外，只有設(shè)置了 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)結(jié)果如下：
另外，如果我們建表不指定約束，那么 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
剛開始學(xué)習(xí)數(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)結(jié)果如下：
可以看到 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 屬性后，顯示的結(jié)果就有所不同了。修改 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 屬性，再進行查找，返回如下結(jié)果：
mysql> select * from t5; +---+------------+ | a | b | +---+------------+ | 1 | 0000000002 | +---+------------+ 1 row in set (0.00 sec)
我們插入一行數(shù)據(jù)也能看到結(jié)果：
這次可以看到 b 的值由原來的 2 變成 0000000002，這就是 zerofill 屬性的作用，如果寬度小于設(shè)定的寬度（這里設(shè)置的是 10），自動填充 0。
要注意的是，這只是最后顯示的結(jié)果，在 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 只是設(shè)置了 zerofill 屬性后的一種格式化輸出而已。
5. 主鍵
主鍵：primary key 用來唯一的約束該字段里面的數(shù)據(jù)，不能重復(fù)，不能為空，一張表中最多只能有一個主鍵；主鍵所在的列通常是整數(shù)類型。
5.1 指定主鍵
創(chuàng)建表的時候直接在字段上指定主鍵。
mysql> create table if not exists test_key (
-> id int unsigned primary key comment '學(xué)號不能為空',
-> 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 主鍵約束
主鍵對應(yīng)的字段中不能重復(fù)，一旦重復(fù)，操作失敗。
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 重新添加主鍵，但是記得把表里面重復(fù)的記錄給刪除掉
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 | | +-------+------------------+------+-----+---------+-------+
結(jié)果如下：
5.5 復(fù)合主鍵
在創(chuàng)建表的時候，在所有字段之后，使用 primary key（主鍵字段列表）來創(chuàng)建主鍵，如果有多個字段作為主鍵，可以使用復(fù)合主鍵。
也就是說，一個主鍵可以被添加到一列，或者多列上。
案例如下，我建一張課程表，然后把學(xué)號和課程號都設(shè)置主鍵
mysql> create table pick_course (
-> id int unsigned comment '學(xué)號',
-> 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：當對應(yīng)的字段，不給值，會自動的被系統(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)
結(jié)果如下：
在插入后獲取上次插入的 AUTO_INCREMENT 的值（批量插入獲取的是第一個值）
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec)
6.1 索引
這里可以簡單的談一下索引。
在關(guān)系數(shù)據(jù)庫中，索引是一種單獨的、物理的對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。
索引的作用相當于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。索引提供指向存儲在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)您指定的排序順序?qū)@些指針排序。
數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應(yīng)于表的 SQL 語句執(zhí)行得更快，可快速訪問數(shù)據(jù)庫表中的特定信息。
7. 唯一鍵
一張表中有往往有很多字段需要唯一性，數(shù)據(jù)不能重復(fù)，但是一張表中只能有一個主鍵：唯一鍵就可以解決表中有多個字段需要唯一性約束的問題。
唯一鍵的本質(zhì)和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空字段不做唯一性比較。
我們先建立一張擁有唯一鍵的學(xué)生表
mysql> create table stu (
-> id char(20) unique comment '這是一個學(xué)生的唯一鍵',
-> 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)結(jié)果如下：
我們再來建一張表：
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è)置了唯一鍵可以，可以避免一些未定義或者誤操作，從而保證插入表里面的數(shù)據(jù)不一樣：
關(guān)于唯一鍵和主鍵的區(qū)別：
- 我們可以簡單理解成，主鍵更多的是標識唯一性的。
- 而唯一鍵更多的是保證在業(yè)務(wù)上，不要和別的信息出現(xiàn)重復(fù)。
乍一聽好像沒啥區(qū)別，我們舉一個例子，假設(shè)一個場景：
- 比如在公司，我們需要一個員工管理系統(tǒng)，系統(tǒng)中有一個員工表，員工表中有兩列信息，一個身份證號碼，一個是員工工號，我們可以選擇身份號碼作為主鍵。
- 而我們設(shè)計員工工號的時候，需要一種約束：而所有的員工工號都不能重復(fù)。具體指的是在公司的業(yè)務(wù)上不能重復(fù)，我們設(shè)計表的時候，需要這個約束，那么就可以將員工工號設(shè)計成為唯一鍵。
- 一般而言，我們建議將主鍵設(shè)計成為和當前業(yè)務(wù)無關(guān)的字段，這樣，當業(yè)務(wù)調(diào)整的時候，我們可以盡量不會對主鍵做過大的調(diào)整。
8. 外鍵
外鍵用于定義主表和從表之間的關(guān)系：外鍵約束主要定義在從表上，主表則必須是有主鍵約束或 unique 約束。當定義外鍵后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或為 null。
語法：
foreign key (字段名) references 主表(列)
案例如下圖所示：
對上面的示意圖進行設(shè)計：
- 先創(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 列建立我們的外鍵約束關(guān)系
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)
然后再對學(xué)生表插入數(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 的學(xué)生，因為沒有這個班級，所以插入不成功
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`))
- 假設(shè)我現(xiàn)在要刪除 人工智能101班，但是因為班里面有學(xué)生，所以無法刪除，這就是外鍵約束
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`))
我們只有把班級里面的學(xué)生刪除，然后刪除班級
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，比如來了一個學(xué)生，目前還沒有分配班級
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)生關(guān)聯(lián)。
- 2、增加約束，來整體保證表跟表之間的完整性。
8.1 如何理解外鍵約束
首先我們承認，這個世界是數(shù)據(jù)很多都是相關(guān)性的。
理論上，上面的例子，我們不創(chuàng)建外鍵約束，就正常建立學(xué)生表，以及班級表，該有的字段我們都有。
此時，在實際使用的時候，可能會出現(xiàn)什么問題？
有沒有可能插入的學(xué)生信息中有具體的班級，但是該班級卻沒有在班級表中？
比如計算機學(xué)院的人工智能專業(yè)只開了【人工智能 1 班】和【人工智能 2 班】，但是在上課的學(xué)生里面竟然有【人工智能 3 班】的學(xué)生（這個班目前并不存在），這很明顯是有問題的。
因為此時兩張表在業(yè)務(wù)上是有相關(guān)性的，但是在業(yè)務(wù)上沒有建立約束關(guān)系，那么就可能出現(xiàn)問題。
解決方案就是通過外鍵完成的。建立外鍵的本質(zhì)其實就是把相關(guān)性交給 MySQL 去審核了，提前告訴 MySQL 表之間的約束關(guān)系，那么當用戶插入不符合業(yè)務(wù)邏輯的數(shù)據(jù)的時候，MySQL 不允許你插入。
9. 綜合案例
有一個商店的數(shù)據(jù)，記錄客戶及購物情況，有以下三個表組成：
1?? 商品表 goods：
- 商品編號 goods_id
- 商品名 goods_name
- 單價 unitprice
- 商品類別 category
- 供應(yīng)商 provider
2?? 客戶表 customer：
- 客戶號 customer_id
- 姓名 name
- 住址 address
- 郵箱 email
- 性別 sex
- 身份證 card_id
3?? 購買表 purchase：
- 購買訂單號 order_id
- 客戶號 customer_id
- 商品號 goods_id
- 購買數(shù)量 nums
要求：
- 每個表的主外鍵
- 客戶的姓名不能為空值
- 郵箱不能重復(fù)
- 客戶的性別（男，女）
先創(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 '供應(yīng)商名稱'
-> );
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 → 關(guān)聯(lián)到 customer.customer_id，表示 “誰買的”。
- goods_id → 關(guān)聯(lián)到 goods.goods_id，表示 “買的什么”。
9.4 主鍵和外鍵
| 表名 | 主鍵 | 外鍵 | 外鍵關(guān)聯(lián) |
|---|---|---|---|
| goods | goods_id | — | — |
| customer | customer_id | — | — |
| purchase | order_id | customer_id, goods_id | customer(customer_id), goods(goods_id) |
關(guān)系圖示：
customer (客戶表)
│
│ customer_id ←───┐
│ │
└──────────────────? purchase (購買表) ───? goods (商品表)
▲ │
│ goods_id │
└──────────────────────┘注意：
- 一個客戶（customer）可以有多筆訂單（purchase）。
- 一個商品（goods）可以被多筆訂單購買。
- 所以這是一個 多對多關(guān)系，中間的 purchase 表是 關(guān)聯(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ū)中關(guān)村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ū)中關(guān)村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 驗證外鍵和關(guān)聯(lián)查詢
執(zhí)行以下 SQL 語句看看結(jié)果是否正確：
-- 查詢所有訂單及關(guān)聯(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;
查詢結(jié)果如下：
到此這篇關(guān)于MySQL表的約束的文章就介紹到這了,更多相關(guān)mysql表的約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本篇文章小編為大家介紹，跟著hsp一步步學(xué)mysql優(yōu)化的方法。需要的朋友參考下2013-04-04
MySQL數(shù)據(jù)庫給表添加索引的實現(xiàn)
在MySQL中,索引是用來加速數(shù)據(jù)庫查詢的一種特殊數(shù)據(jù)結(jié)構(gòu),當我們需要查詢數(shù)據(jù)庫中某些數(shù)據(jù)的時候,如果數(shù)據(jù)庫中有索引,就可以避免全表掃描,從而提高查詢速度,本文就介紹了如何給表添加索引,感興趣的可以了解一下2023-08-08
CentOS下將MySQL 5.1升級到MySQL 5.5的步驟
這篇文章主要介紹了CentOS下將MySQL 5.1升級到MySQL 5.5的步驟,需要的朋友可以參考下2015-08-08
這篇文章主要介紹了mysql中TINYINT的取值范圍,需要的朋友可以參考下2014-08-08
Mac OS10.11下mysql5.7.12 安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了Mac OS10.11下mysql5.7.12 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
ERROR 1862 (HY000): Your password has expired. To log in you
當你在安裝 MySQL過程中，通過mysqld --initialize 初始化 mysql 操作后，生成臨時密碼后，沒有直接進行 MySQL連接，中途重啟服務(wù)或者重啟機器等，導(dǎo)致密碼失效問題，怎么處理呢，感興趣的朋友一起看看吧2019-11-11
今天想提到的是線上一個4G的RDS實例，發(fā)生了OOM（out of memory）的問題，MySQL進程被直接Kill掉了。在解釋這個問題的時候，我們首先需要從Linux系統(tǒng)內(nèi)存分配策略講起2016-07-07
MySql Installer 8.0.18可視化安裝教程圖文詳解
這篇文章主要介紹了MySql Installer 8.0.18可視化安裝教程，本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-12-12
文章介紹了如何使用Sharding-JDBC對訂單表進行水平分表,并通過配置分片規(guī)則、數(shù)據(jù)操作和測試驗證了分表的實現(xiàn),同時,文章還討論了Sharding-JDBC的多種集成方式和4種分片策略的實現(xiàn)方法,包括標準分片策略、復(fù)合分片策略、行表達式分片策略和Hint分片策略2025-02-02
在工作中遇到時間處理,學(xué)習(xí)了一下SQL的日期處理方面,下面這篇文章主要給大家介紹了關(guān)于sql語句中日期相減操作的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09