快捷導(dǎo)航

MySQL主鍵約束和外鍵約束詳解

更新時(shí)間：2023年03月13日 09:51:26 作者：程序猿教你打籃球

主鍵約束，說(shuō)白了就是 not null + unique，主鍵也是在插入數(shù)據(jù)的時(shí)候先進(jìn)行查詢(xún)，而 MySQL 則會(huì)對(duì) unique，primary key 這樣的列自動(dòng)的添加索引(后續(xù)介紹)，來(lái)提高查詢(xún)的效率，這篇文章主要介紹了MySQL主鍵約束和外鍵約束,需要的朋友可以參考下

1、PRIMARY KEY

上期我們講述了 not null 和 unique 約束，而本期的第一個(gè)約束就是這倆的結(jié)合體，也成為主鍵約束。

主鍵約束，說(shuō)白了就是 not null + unique，主鍵也是在插入數(shù)據(jù)的時(shí)候先進(jìn)行查詢(xún)，而 MySQL 則會(huì)對(duì) unique，primary key 這樣的列自動(dòng)的添加索引(后續(xù)介紹)，來(lái)提高查詢(xún)的效率。

● 在實(shí)際開(kāi)發(fā)中，大部分的表，一般都會(huì)帶有一個(gè)主鍵，主鍵往往是一個(gè)整數(shù)表示的 id。

create table student (
    id int primary key,
    name varchar(10)
);

只要你給改列設(shè)置了主鍵，意味著后續(xù)插入的數(shù)據(jù)，既不能重復(fù)，也不能為空！

● 在 MySQL 中，一個(gè)表中只能有一個(gè)主鍵，不能有多個(gè)。

create table student (
    id int primary key,
    name varchar(10) primary ke
);
-- ERROR 1068 (42000): Multiple primary key defined

● 雖然主鍵不能有多個(gè)，但支持將多個(gè)列放到一起，共同作為主鍵，稱(chēng)為聯(lián)合主鍵。

create table student (
    id int,
    name varchar(10),
    primary key (id, name)
);

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
-- 2 rows in set (0.00 sec)

這樣我們就能發(fā)現(xiàn) Key 這一列有兩個(gè) PRI 了，PRI 是主鍵的簡(jiǎn)寫(xiě)，表示是主鍵字段，同時(shí)也能看到 Null 這一列為 NO，所以主鍵包含了 not null 和 unique 的特性。

● 主鍵還有一個(gè)非常常用的用法，使用 MySQL 自帶的自增主鍵作為主鍵的值。

create table student (
    id int primary key auto_increment,
    name varchar(10)
);

這里我們就來(lái)插入數(shù)據(jù)試一試：

insert into student value 
    (88, '李四'),
    (12, '張三');

這里仍然可以插入成功，由此能發(fā)現(xiàn)設(shè)置了自增主鍵仍然能自定義值，那么下面如果不自定義 id 的值呢？會(huì)自動(dòng)生成多少呢？

insert into student(name) values('王五');
-- Query OK, 1 row affected (0.00 sec)
 
select * from student;
+----+--------+
| id | name   |
+----+--------+
| 12 | 張三   |
| 88 | 李四   |
| 89 | 王五   |
+----+--------+
-- 3 rows in set (0.00 sec)

注意：這里可以看到，我們插入是李四，張三，但是查詢(xún)卻是張三在前李四在后，所以 MySQL 并未保證插入順序和取出是順序是一致的！

再者這里可以發(fā)現(xiàn)，自增主鍵插入數(shù)據(jù)對(duì)應(yīng)字段不給值時(shí)，使用最大值+1。那如果我把王五刪了，再次插入一條數(shù)據(jù)，自增主鍵會(huì)是多少呢？

delete from student where name = '王五';
-- Query OK, 1 row affected (0.00 sec)
 
insert into student(name) values('趙六');
-- Query OK, 1 row affected (0.00 sec)
 
select * from student;
+----+--------+
| id | name   |
+----+--------+
| 12 | 張三   |
| 88 | 李四   |
| 90 | 趙六   |
+----+--------+
-- 3 rows in set (0.00 sec)

看到?jīng)]！并不是按照當(dāng)前列的最大值來(lái)自增主鍵的，你可以理解為 MySQL 有個(gè)記錄主鍵最大值的玩意，是按照這個(gè)之前主鍵最大值自增的！刪除了數(shù)據(jù)，不影響主鍵的最大值！

我們?cè)賮?lái)看一個(gè)操作：

insert into student values(null, '孫七');

這個(gè)操作并不是插入 null 數(shù)據(jù)，而是交給數(shù)據(jù)庫(kù)使用自增主鍵！

這里又有一個(gè)疑問(wèn)了，如果數(shù)據(jù)量太大，一個(gè)服務(wù)器存不下那么多數(shù)據(jù)怎么辦？那就要采用分庫(kù)分表的操作了，多臺(tái)服務(wù)器來(lái)存儲(chǔ)了，本質(zhì)上就是把一張大表，分成多張小表，每個(gè)服務(wù)器分別只存一部分大大數(shù)據(jù)，這就可以理解成分布式處理了，

問(wèn)題來(lái)了，上述這樣的情況，還能使用自增主鍵嗎？

這里涉及到一個(gè) "分布式系統(tǒng)中唯一 id 生成算法" ，也就是全局唯一 id，常見(jiàn)的即：

實(shí)現(xiàn)公式：時(shí)間戳+主機(jī)編號(hào)+隨機(jī)因子 => 全局唯一的 id。

當(dāng)然感興趣的可以下來(lái)進(jìn)一步了解下，這里就不過(guò)多闡述了。

2、FOREIGN KEY

外鍵約束，說(shuō)白了就是兩個(gè)表之間相互約束。

注意：外鍵是用于關(guān)聯(lián)其他表的主鍵或唯一鍵！

foreign key (字段名) references 主表(列)

這里我們舉個(gè)例子，在學(xué)校里，有很多個(gè)班級(jí)，每個(gè)班級(jí)里有很多學(xué)生，也就是一個(gè)學(xué)生對(duì)應(yīng)一個(gè)班級(jí)，一個(gè)班級(jí)對(duì)應(yīng)多個(gè)學(xué)生，假設(shè)某某學(xué)校，只有三個(gè) java 班級(jí)，分別是 java1班，java2班，java3班，每個(gè)班級(jí)又有若干個(gè)學(xué)生，現(xiàn)在開(kāi)學(xué)了，學(xué)生去學(xué)校報(bào)道，報(bào)道的時(shí)候呢會(huì)登記你的班級(jí)，志愿者一問(wèn)，同學(xué)你哪個(gè)班的呀？我說(shuō)：我 java2 班的，于是順利登記成功，后面又來(lái)了一個(gè)同學(xué)，同樣的問(wèn)題，同學(xué)你哪個(gè)班的呀？我 java6班，志愿者一查詢(xún)，咱學(xué)校沒(méi)有 java6班呀，于是那個(gè)同學(xué)就被老師帶走調(diào)查了...

上述只是一個(gè)故事，可以把我們自己想象成一條數(shù)據(jù)，當(dāng)然登記信息肯定不僅僅是登記班級(jí)，還有有學(xué)號(hào) 姓名性別身份證班級(jí)號(hào)... 而這里我們的班級(jí)號(hào)不是隨便哪個(gè)都可以的，是有固定的幾個(gè)班級(jí)號(hào)，如果這幾個(gè)班級(jí)號(hào)中沒(méi)有你報(bào)的班級(jí)號(hào)，那么你的信息，就錄不上去，也就是一個(gè)錯(cuò)誤的信息。假設(shè)這幾個(gè)班級(jí)號(hào)放在一起，對(duì)應(yīng)一張班級(jí)表，咱們登記登記的信息，對(duì)應(yīng)學(xué)生表，那么就是兩張表之間指定字段的約束。

有了上述鋪墊，我們就來(lái)用代碼來(lái)理解一下：

● 首先創(chuàng)建一張班級(jí)表 classes，id 為主鍵：

create table classes (
    id int primary key,
    name varchar(10)
);

這樣 classes 班級(jí)表就建好了，這里有個(gè)問(wèn)題，為什么 id 要作為主鍵呢？

答：首先這里的 id 對(duì)應(yīng)班級(jí)號(hào)，name 是班級(jí)名稱(chēng)，而外鍵是用于關(guān)聯(lián)其他表的主鍵或唯一鍵，所以說(shuō)這里的 id 字段，要不是主鍵，要不是唯一鍵。

未來(lái)學(xué)生表會(huì)有一個(gè)字段擁有外鍵屬性，來(lái)關(guān)聯(lián)這個(gè) classes 的 id 字段！??！

● 創(chuàng)建學(xué)生表 student 指定 classId 為外鍵，關(guān)聯(lián) classes 表中 id 字段

create table student (
    id int,
    name varchar(10),
    sex varchar(1),
    classId int,
    foreign key (classId) references classes(id)
);

這段 sql 語(yǔ)句最后一條，foreign key (classId) references classes(id)，這條語(yǔ)句表示，將創(chuàng)建的 student 表中 classId 字段作為外鍵，于 classes 表中 id 字段關(guān)聯(lián)！

這兩張表是創(chuàng)建好了，現(xiàn)在得創(chuàng)建數(shù)據(jù)了，為 classes 表中創(chuàng)建三個(gè)班級(jí)數(shù)據(jù)：

insert into classes value 
    (1, 'java1班'),
    (2, 'java2班'),
    (3, 'java3班');

這樣就能表示有存在了三個(gè)班級(jí)，分別是 1班，2班，3班，如果接下來(lái)我去登記信息，錄入的班級(jí)在這三個(gè)班之中，那就毫無(wú)問(wèn)題：

insert into student values (230115, '籃球哥', '男', 2);
-- Query OK, 1 row affected (0.00 sec)

這里是不是就如同我們上面舉例說(shuō)明的這樣，我是 java2 班的，而 classId 錄入了一個(gè) 2，關(guān)聯(lián) classes 的 id 字段 2，對(duì)應(yīng)的就是 java2 班。

那如果后來(lái)的同學(xué)，錄入一個(gè) java6 班呢？

insert into student values (230205, '小王', '男', 6);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `classes` (`id`))

那么此處的外鍵約束的含義，要求了 student 里的 classId 務(wù)必要在 classes 表的 id 列中存在！

相信看到這，大家都能明白外鍵約束的作用了，student 表中的數(shù)據(jù)，要依賴(lài) classes 表的數(shù)據(jù)，而 classes 表的數(shù)據(jù)要對(duì) student 表產(chǎn)生約束力，就像父親對(duì) 孩子有約束力。此處起到約束作用的表我們稱(chēng)為 "父表"，被約束的表我們稱(chēng)為 "子表"。

看到這里啊，說(shuō)白了就是父表對(duì) 子表起到了約束作用，限制了約束的字段的取值.

這里我不禁想到一句話(huà)，當(dāng)你在凝視深淵的時(shí)候，深淵也在凝視你！

我們表面上看確實(shí)是父表對(duì) 子表起到約束，但反過(guò)來(lái)其實(shí) 子表也限制著父表！

這里我們嘗試刪除父表中 id 為 2 的數(shù)據(jù)，也就是刪除 java1班：

delete from classes where id = 2;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `classes` (`id`))

發(fā)現(xiàn)報(bào)錯(cuò)了，其實(shí)我們想也能想到，如果真能刪除，那子表中 classesId = 2 的那一條數(shù)據(jù)不就尷尬了嗎？那這外鍵約束建了又像沒(méi)有建一樣，所以就僵住了，顯然是不允許這樣的情況發(fā)生的！

當(dāng)然 classes 表中 id 為 1 的數(shù)據(jù)可以刪除，因?yàn)?子表并不存在 classId 為 1 的數(shù)據(jù)嘛：

delete from classes where id = 1;
-- Query OK, 1 row affected (0.00 sec)

問(wèn)題：可以先建子表再建父表嗎？

顯然是不行的呀，當(dāng)你建子表指定外鍵約束的時(shí)候，你父表都沒(méi)有，怎么建約束？

3、表的設(shè)計(jì)

表的設(shè)計(jì)算是一個(gè)比較抽象的概念，有了一定經(jīng)驗(yàn)后，會(huì)更好理解，結(jié)合后期的項(xiàng)目設(shè)計(jì)，再來(lái)理解表設(shè)計(jì)，可能會(huì)更好，這里就簡(jiǎn)單介紹下常見(jiàn)設(shè)計(jì)。

3.1 一對(duì)一

比如說(shuō)我們每個(gè)人都有的身份證，每個(gè)身份證id，就對(duì)應(yīng)一個(gè)人，這就是一對(duì)一關(guān)系。

這種一對(duì)一關(guān)系可以如何創(chuàng)建表呢？

● 第一種方案

create table person (
    id int primary key,
    name varchar(10),
    address varchar(50),
    sex varchar(1)
);

把身份證id和姓名直接放在一張表里.

● 第二種方案

create table person (
    id int primary key,
    name varchar(10)
);
create table account (
    accountId int primary key,
    personName varchar(10),
    address varchar(50),
    sex varchar(1),
    id int,
    foreign key (id) references person(id)
);

第二種方案則是身份證號(hào)和姓名放在不同的表里，相互關(guān)聯(lián)。