MySQL主鍵約束和外鍵約束怎么設(shè)置

這篇“MySQL主鍵約束和外鍵約束怎么設(shè)置”文章的知識點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL主鍵約束和外鍵約束怎么設(shè)置”文章吧。

1、PRIMARY KEY

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

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

● 在實(shí)際開發(fā)中，大部分的表，一般都會帶有一個(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è)列放到一起，共同作為主鍵，稱為聯(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 是主鍵的簡寫，表示是主鍵字段，同時(shí)也能看到 Null 這一列為 NO，所以主鍵包含了 not null 和 unique 的特性。

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

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

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

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

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

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)

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

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

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)前列的最大值來自增主鍵的，你可以理解為 MySQL 有個(gè)記錄主鍵最大值的玩意，是按照這個(gè)之前主鍵最大值自增的！刪除了數(shù)據(jù)，不影響主鍵的最大值！

我們再來看一個(gè)操作：

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

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

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

問題來了，上述這樣的情況，還能使用自增主鍵嗎？

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

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

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

2、FOREIGN KEY

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

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

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

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

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

有了上述鋪墊，我們就來用代碼來理解一下：

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

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

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

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

未來學(xué)生表會有一個(gè)字段擁有外鍵屬性，來關(guān)聯(lián)這個(gè) classes 的 id 字段?。。?/p>

● 創(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 語句最后一條，foreign key (classId) references classes(id)，這條語句表示，將創(chuàng)建的 student 表中 classId 字段作為外鍵，于 classes 表中 id 字段關(guān)聯(lián)！

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

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

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

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

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

那如果后來的同學(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ù)，要依賴 classes 表的數(shù)據(jù)，而 classes 表的數(shù)據(jù)要對 student 表產(chǎn)生約束力，就像 父親 對 孩子 有約束力。此處起到約束作用的表我們稱為 "父表"，被約束的表我們稱為 "子表"。

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

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

我們表面上看確實(shí)是 父表 對 子表 起到約束，但反過來其實(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ù)不就尷尬了嗎？那這外鍵約束建了又像沒有建一樣，所以就僵住了，顯然是不允許這樣的情況發(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)

問題：可以先建子表再建父表嗎？

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

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

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

3.1 一對一

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

這種一對一關(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)
);

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

3.2 一對多

這個(gè)例子很像我們之前說過的，一個(gè)班級有多個(gè)學(xué)生，一個(gè)學(xué)生對應(yīng)一個(gè)班級，這里就不在贅述。

3.3 多對多

比如說學(xué)校的一門課程，可以被多名學(xué)生選修，而一名學(xué)生可以選修多門課程，這就是多對多的關(guān)系：

這里我們只需要讓 student 表 studentId 字段 約束 student_course 表的 studentId 字段，以及 course 表  courseId 字段約束 student_course 表的 courseId 字段即可。

以上就是關(guān)于“MySQL主鍵約束和外鍵約束怎么設(shè)置”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對大家有幫助，若想了解更多相關(guān)的知識內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道。