MySQL——約束(constraint)詳解

該部落格說說關於資料庫中一個重要的知識點——約束

一、什麼是約束

約束英文：constraint

約束實際上就是表中資料的限制條件

---

二、約束作用

表在設計的時候加入約束的目的就是為了保證表中的記錄完整和有效

• 比如name欄位中要讓其使用者名稱不重複，這就需要新增約束。或者必須註冊的時候需要新增郵箱等

---

三、約束種類

• 非空約束(not null)
• 唯一性約束(unique)
• 主鍵約束(primary key) PK
• 外來鍵約束(foreign key) FK
• 檢查約束(目前MySQL不支援、Oracle支援)

下面將逐一介紹以上約束

---

四、非空約束

用not null約束的欄位不能為null值，必須給定具體的資料

建立表，給欄位新增非空約束（建立使用者表，使用者名稱不能為空）

    mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.08 sec)

如果沒有插入name欄位資料，則會報錯

mysql> insert into t_user (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

---

五、唯一性約束

unique約束的欄位，具有唯一性，不可重複，但可以為null

建立表，保證郵箱地址唯一(列級約束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique
    -> );
Query OK, 0 rows affected (0.03 sec)

1、表級約束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128），
    -> unique(email)
    -> );

如果插入相同email會報錯

mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com');
ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email'

2、使用表級約束，給多個欄位聯合約束

聯合約束，表示兩個或以上的欄位同時與另一條記錄相等，則報錯

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> unique(name,email)
    -> );
Query OK, 0 rows affected (0.01 sec)

插入第一條資料

mysql> insert into t_user(id,name,email) values(1,'xxx','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第二條資料如果是與聯合欄位中的一條相同另一條相同，也是可以的

mysql> insert into t_user(id,name,email) values(2,'mmm','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第三條資料，如果與聯合欄位都相同，則報錯

mysql> insert into t_user(id,name,email) values(3,'mmm','qq.com');
ERROR 1062 (23000): Duplicate entry 'mmm-qq.com' for key 'name'

3、表級約束可以給約束起名字(方便以後通過這個名字來刪除這個約束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> constraint t_user_email_unique unique(email)
    -> );
Query OK, 0 rows affected (0.06 sec)

constraint是約束關鍵字，t_user_email_unique自己取的名字

例：使用者名稱既不能為空，也不能重複

name varchar(32) not null unique

---

六、主鍵約束(primary key)PK

表設計時一定要有主鍵

1、主鍵涉及術語

• 主鍵約束
• 主鍵欄位
• 主鍵值

2、以上三種術語關係

表中的某個欄位新增主鍵約束後，該欄位為主鍵欄位，主鍵欄位中出現的每一個資料都稱為主鍵值

3、主鍵約束與“not null unique”區別

給某個欄位新增主鍵約束之後，該欄位不能重複也不能為空，效果和”not null unique”約束相同，但是本質不同。

主鍵約束除了可以做到”not null unique”之外，還會預設新增”索引——index”

4、一張表應該有主鍵欄位，如果沒有，表示該表無效

• 主鍵值：是當前行資料的唯一標識、是當前行資料的身份證號
• 即使表中兩行記錄相關資料相同，但由於主鍵值不同，所以也認為是兩行不同的記錄

5、按主鍵約束的欄位數量分類

無論是單一主鍵還是複合主鍵，一張表主鍵約束只能有一個(約束只能有一個，但可以作用到好幾個欄位)

• 單一主鍵：給一個欄位新增主鍵約束
• 複合主鍵：給多個欄位聯合新增一個主鍵約束(只能用表級定義)

單一主鍵(列級定義)

mysql> create table t_user(
    -> id int(10) primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

單一主鍵(表級定義)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> constraint t_user_id_pk primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

複合主鍵(表級定義)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique,
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.05 sec)

6、在MySQL資料庫提供了一個自增的數字，專門用來自動生成主鍵值，主鍵值不用使用者維護，自動生成，自增數從1開始，以1遞增(auto_increment)

mysql> create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

插入兩行記錄，id主鍵值會自動增加

mysql> insert into t_user(name) values('jay');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t_user(name) values('man');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
+----+------+
2 rows in set (0.00 sec)

---

七、外來鍵約束(foreign key)FK

只能是表級定義（如以下例子）

foreign key(classno) references t_class(cno)

什麼是外來鍵

若有兩個表A、B，id是A的主鍵，而B中也有id欄位，則id就是表B的外來鍵，外來鍵約束主要用來維護兩個表之間資料的一致性。

A為基本表，B為資訊表

1、外來鍵涉及到的術語

• 外來鍵約束
• 外來鍵欄位
• 外來鍵值

2、外來鍵約束、外來鍵欄位、外來鍵值之間的關係

某個欄位新增外來鍵約束之後，該欄位稱為外來鍵欄位，外來鍵欄位中每個資料都是外來鍵值

3、按外來鍵約束的欄位數量分類

• 單一外來鍵：給一個欄位新增外來鍵約束
• 複合外來鍵：給多個欄位聯合新增一個外來鍵約束

4、一張表可以有多個外來鍵欄位（與主鍵不同）

5、分析場景

設計資料庫表，用來儲存學生和班級資訊

兩種方案

方案一：將學生資訊和班級資訊儲存到一張表

sno     sname      classno      cname
1       jay         100         浙江省第一中學高三1班
2       lucy        100         浙江省第一中學高三1班
3       king        200         浙江省第一中學高三2班

缺點：資料冗餘，比如cname欄位的資料重複太多

方案二：將學生資訊和班級資訊分開兩張表儲存

學生表（新增單一外來鍵）

sno(pk)     sname       classno(fk)
1           jack        100
2           lucy        100
3           king        200

班級表

cno(pk)     cname
100         浙江省第一中學高三1班
200         浙江省第一中學高三2班

結論

為了保證學生表中的classno欄位中的資料必須來自於班級表中的cno欄位中的資料，有必要給學生表中的classno欄位新增外來鍵約束

注意點

• 外來鍵值可以為null
• 外來鍵欄位去引用一張表的某個欄位的時候，被引用的欄位必須具有unique約束
• 有了外來鍵引用之後，表分為父表和子表
  
  • 班級表：父表
  • 學生表：子表
• 建立先建立父表
• 刪除先刪除子表資料
• 插入先插入父表資料

儲存學生班級資訊

mysql> drop table if exists t_student;
mysql> drop table if exists t_class;

mysql> create table t_class(
    -> cno int(10) primary key,
    -> cname varchar(128) not null unique
    -> );

mysql> create table t_student(
    -> sno int(10) primary key auto_increment,
    -> sname varchar(32) not null,
    -> classno int(3),
    -> foreign key(classno) references t_class(cno)
    -> );

mysql> insert into t_class(cno,cname) values(100,'aaaaaaxxxxxx');
mysql> insert into t_class(cno,cname) values(200,'oooooopppppp');
mysql> insert into t_student(sname,classno) values('jack',100);
mysql> insert into t_student(sname,classno) values('lucy',100);
mysql> insert into t_student(sname,classno) values('king',200);

班級表t_class

mysql> select * from t_class;
+-----+--------------+
| cno | cname        |
+-----+--------------+
| 100 | aaaaaaxxxxxx |
| 200 | oooooopppppp |
+-----+--------------+

學生表t_student

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | jack  |     100 |
|   2 | lucy  |     100 |
|   3 | king  |     200 |
+-----+-------+---------+

上表中找出每個學生的班級名稱

mysql> select s.*,c.* from t_student s join t_class c on s.classno=c.cno;
+-----+-------+---------+-----+--------------+
| sno | sname | classno | cno | cname        |
+-----+-------+---------+-----+--------------+
|   1 | jack  |     100 | 100 | aaaaaaxxxxxx |
|   2 | lucy  |     100 | 100 | aaaaaaxxxxxx |
|   3 | king  |     200 | 200 | oooooopppppp |
+-----+-------+---------+-----+--------------+

結論

以上是典型的一對多的設計：在多個地方加外來鍵(子表加外來鍵)