MySQL數(shù)據(jù)庫外鍵?foreing?key
更新時間：2022年06月09日 10:44:03 作者：彭世瑜
這篇文章主要介紹了MySQL數(shù)據(jù)庫外鍵?foreing?key，外鍵表示了兩個實體之間的聯(lián)系，更多相關(guān)介紹需要的小伙伴可以參考一下
前言：
外鍵表示了兩個實體之間的聯(lián)系
外鍵 foreing key: A表中的一個字段的值指向另B表的主鍵
- B: 主表
- A: 從表
主表：主鍵（主關(guān)鍵字） = 從表：外鍵（外關(guān)鍵字）
1、外鍵操作
1.1、增加外鍵
基本語法：
方式一：創(chuàng)建表的時候增加外鍵
[constraint `外鍵名`] foreign key (外鍵字段) references 主表(主鍵);
方式二：創(chuàng)建表后增加外鍵
alter table 從表 add [constraint `外關(guān)鍵字`] foreign key (外鍵字段) references 主表(主鍵);
示例1： 創(chuàng)建表的時候增加外鍵
-- 創(chuàng)建外鍵
create table my_foreign(
id int primary key auto_increment,
name varchar(10) not null,
class_id int,
-- 創(chuàng)建時，會自動增加普通索引
foreign key (class_id) references my_class(id)
);
mysql> show create table my_foreign;
CREATE TABLE `my_foreign` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`),
CONSTRAINT `my_foreign_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
mysql> desc my_foreign;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)MUL多索引 外鍵本身也是索引
示例2： 創(chuàng)建表后增加外鍵
-- 查看原來的表
mysql> desc my_class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> show create table my_class;
CREATE TABLE `my_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
mysql> desc my_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
mysql> show create table my_student\G
*************************** 1. row ***************************
Table: my_student
Create Table: CREATE TABLE `my_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-- 增加外鍵
alter table my_student add constraint fk_class_id foreign key (class_id) references my_class(id);
mysql> show create table my_student\G
*************************** 1. row ***************************
Table: my_student
Create Table: CREATE TABLE `my_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_class_id` (`class_id`),
CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1.2、刪除外鍵
外鍵不允許修改，只能先刪除再添加
alter table 從表 drop foreign key 外鍵名字;
示例：
alter table my_student drop foreign key `fk_class_id`;
mysql> show create table my_student\G
*************************** 1. row ***************************
Table: my_student
Create Table: CREATE TABLE `my_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_class_id` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci外鍵不會刪除普通索引，只會刪除外鍵
刪除普通索引：
alter table 表名 drop index 索引名字;
1.3、外鍵的基本要求
- 外鍵字段需要與關(guān)聯(lián)的主表的主關(guān)鍵字段類型完全一致
- 基本屬性也要相同
- 如果是表后增加外鍵，對數(shù)據(jù)還有一定的要求（從表數(shù)據(jù)與主表的關(guān)聯(lián)關(guān)系）
- 外鍵只能使用innodb存儲引擎，myisam不支持
2、外鍵約束
外鍵約束：通過建立外鍵關(guān)系后，對主表和從表都會有一定的數(shù)據(jù)約束效律
2.1、約束的基本概念
當(dāng)外鍵產(chǎn)生時，外鍵所在的表（從表）會受制于主表數(shù)據(jù)的存在，從而導(dǎo)致數(shù)據(jù)不能進行某些不符合規(guī)范的操作
不能插入主表不存在的數(shù)據(jù)
如果一張表被其他表外鍵引入，那么該表的數(shù)據(jù)操作不能隨意，必須保證從表數(shù)據(jù)的有效性，不能隨意刪除一個被從表引入的記錄
mysql> select * from my_class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 3 | 三班 |
| 2 | 二班 |
+----+--------+
insert into my_foreign (name, class_id) values ('張飛', 1);
Query OK, 1 row affected (0.01 sec)
-- 主表沒有id=4的記錄，從表不能插入該數(shù)據(jù)
insert into my_foreign (name, class_id) values ('張飛', 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydatabase`.`my_foreign`, CONSTRAINT `my_foreign_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`))
mysql> select * from my_foreign;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | 張飛 | 1 |
+----+--------+----------+
1 row in set (0.00 sec)
-- 從表中引用了id=1的記錄，該數(shù)據(jù)不能被刪除
delete from my_class where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydatabase`.`my_foreign`, CONSTRAINT `my_foreign_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`))2.2、外鍵約束的概念
基本語法：
add foreign key (外鍵字段) references 主表(主鍵) on 約束模式;
約束模式有三種：
- district 嚴(yán)格模式 默認(rèn)，不允許操作
- cascade 級聯(lián)模式，一起操作，主表變化，從表的數(shù)據(jù)也跟著變化
- set null 置空模式 主表變化（刪除）,從表對應(yīng)記錄設(shè)置，前提是從表中對應(yīng)的外鍵字段允許為空
外鍵約束的主要對象是主表操作，從表就是不能插入主表不存在的數(shù)據(jù)
通常在進行約束的時候，需要制定操作，update 和 delete
常用的模式：
-- 更新級聯(lián), 刪除置空, 空格隔開 on update cascade on delete set null;
-- 增加約束模式
alter table my_student
add foreign key (class_id) references my_class(id)
on update cascade on delete set null;
mysql> show create table my_student\G
*************************** 1. row ***************************
Table: my_student
Create Table: CREATE TABLE `my_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`),
CONSTRAINT `my_student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
mysql> select * from my_class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 3 | 三班 |
| 2 | 二班 |
+----+--------+
3 rows in set (0.00 sec)
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 劉備 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 張飛 | 2 | 21 | 1 |
| 5 | 關(guān)羽 | NULL | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)
-- 更新主表
update my_class set id = 4 where id = 2;
-- 從表中所有class_id=2 的記錄被修改為了 class_id=4
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 劉備 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 4 | 20 | 2 |
| 4 | 張飛 | 4 | 21 | 1 |
| 5 | 關(guān)羽 | NULL | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
-- 刪除主表數(shù)據(jù)
delete from my_class where id = 4;
-- 從表中記錄class_id=4被修改為class_id=null;
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 劉備 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | NULL | 20 | 2 |
| 4 | 張飛 | NULL | 21 | 1 |
| 5 | 關(guān)羽 | NULL | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)
2.3、約束的作用
保證數(shù)據(jù)的完整性，主表與從表的數(shù)據(jù)要一致，正是因為外鍵有非常強大的數(shù)據(jù)約束作用，而且可能導(dǎo)致數(shù)據(jù)再后臺變化的不可控性，導(dǎo)致程序在設(shè)計開發(fā)邏輯的時候，沒有辦法很好的把握數(shù)據(jù)，所以外鍵很少使用
到此這篇關(guān)于MySQL數(shù)據(jù)庫外鍵 foreing key的文章就介紹到這了,更多相關(guān)MySQL foreing key內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
lnmp重置mysql數(shù)據(jù)庫root密碼的兩種方法
這篇文章給大家介紹了lnmp重置mysql數(shù)據(jù)庫root密碼的兩種方法，第一種方法通過腳本重置密碼，第二種方法通過命令修改，具體操作方法大家參考下本文2017-07-07
這篇文章主要為大家詳細(xì)介紹了mysql安裝配置教程，以圖文結(jié)合的方式為大家分享了mysql安裝步驟，感興趣的小伙伴們可以參考一下2016-06-06
這篇文章主要介紹了mysql實現(xiàn)表內(nèi)增加一個字段并賦值，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
Mysql 刪除數(shù)據(jù)庫drop database詳細(xì)介紹
在mysql中，我們可以使用DROP DATABASE來刪除數(shù)據(jù)庫，并且數(shù)據(jù)庫中所有表也隨之刪除。本文通過實例向各位碼農(nóng)介紹DROP DATABASE的使用方法，需要的朋友可以參考下2016-11-11
詳解用SELECT命令在MySQL執(zhí)行查詢操作的教程
這篇文章主要介紹了詳解用SELECT命令在MySQL執(zhí)行查詢操作的教程,本文中還給出了基于PHP腳本的操作演示,需要的朋友可以參考下2015-05-05
Mysql循環(huán)插入數(shù)據(jù)的實現(xiàn)
這篇文章主要介紹了Mysql循環(huán)插入數(shù)據(jù)的實現(xiàn)過程，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
MySQL中case?when的兩種基本用法及區(qū)別總結(jié)
在mysql中case when用于計算條件列表并返回多個可能結(jié)果表達(dá)式之一,下面這篇文章主要給大家介紹了關(guān)于MySQL中case?when的兩種基本用法及區(qū)別的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-05-05
本文將會介紹 MySQL 的事務(wù) ACID 特性和 MySQL 事務(wù)控制流程的語法，并介紹事務(wù)并發(fā)處理中可能出現(xiàn)的異常情況，比如臟讀、幻讀、不可重復(fù)讀等等，最后介紹事務(wù)隔離級別。感興的小伙伴可以一起來學(xué)習(xí)
2021-08-08 
這篇文章主要介紹了MySQL聯(lián)合索引用法,結(jié)合實例形式分析了MySQL聯(lián)合索引的具體定義與使用方法,需要的朋友可以參考下
2016-09-09