欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL數(shù)據(jù)庫中外鍵(foreign?key)用法詳解

 更新時間：2023年10月27日 08:49:32   作者：知其黑、受其白  
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫中外鍵(foreign?key)的相關(guān)資料,MySQL 外鍵約束可以用來保證表與表之間的關(guān)系完整性,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

前言

在MySQL中，我們都對主鍵比較了解，知道主鍵的主要作用是唯一區(qū)分表中的各個行；
但是，對于外鍵（foreign key） 比較陌生。

一、外鍵作用及其限制條件

1 外鍵的定義

外鍵是某個表中的一列，它包含在另一個表的主鍵中。

外鍵也是索引的一種，是通過一張表中的一列指向另一張表中的主鍵，來對兩張表進行關(guān)聯(lián)。

一張表可以有一個外鍵，也可以存在多個外鍵，與多張表進行關(guān)聯(lián)。

2 外鍵的作用

外鍵的主要作用是保證數(shù)據(jù)的一致性和完整性，并且減少數(shù)據(jù)冗余。
主要體現(xiàn)在以下兩個方面：

阻止執(zhí)行

  • 從表插入新行，其外鍵值不是主表的主鍵值便阻止插入。
  • 從表修改外鍵值，新值不是主表的主鍵值便阻止修改。
  • 主表刪除行，其主鍵值在從表里存在便阻止刪除(要想刪除，必須先刪除從表的相關(guān)行)。
  • 主表修改主鍵值，舊值在從表里存在便阻止修改(要想修改，必須先刪除從表的相關(guān)行)。

級聯(lián)執(zhí)行

  • 主表刪除行，連帶從表的相關(guān)行一起刪除。
  • 主表修改主鍵值，連帶從表相關(guān)行的外鍵值一起修改。

3 外鍵創(chuàng)建限制

父表必須已經(jīng)存在于數(shù)據(jù)庫中，或者是當(dāng)前正在創(chuàng)建的表。

如果是后一種情況，則父表與子表是同一個表，這樣的表稱為自參照表，這種結(jié)構(gòu)稱為自參照完整性。

必須為父表定義主鍵。

外鍵中列的數(shù)目必須和父表的主鍵中列的數(shù)目相同。

兩個表必須是 InnoDB 表，MyISAM 表暫時不支持外鍵。

外鍵列必須建立了索引，MySQL 4.1.2 以后的版本在建立外鍵時會自動創(chuàng)建索引，但如果在較早的版本則需要顯式建立。

外鍵關(guān)系的兩個表的列必須是數(shù)據(jù)類型相似，也就是可以相互轉(zhuǎn)換類型的列，比如 int 和tinyint 可以，而 int 和 char 則不可以；

二、外鍵創(chuàng)建方法

可以在創(chuàng)建表時創(chuàng)建外鍵，也可以在已有的表中增加外鍵。

我們主要講第二種方式創(chuàng)建外鍵。

1 創(chuàng)建外鍵的語法

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY(外鍵字段名)
REFERENCES 外表表名(主鍵字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中，ON DELETE 和 ON UPDATE 表示事件觸發(fā)限制，各參數(shù)意義如下：

參數(shù)意義
RESTRICT限制外表中的外鍵改動（默認值，也是最安全的設(shè)置）
CASCADE跟隨外鍵改動
SET NULL設(shè)為null值
NO ACTION無動作
SET DEFAULT設(shè)為默認值

2 舉例

（1）創(chuàng)建兩張表

CREATE TABLE student
(
	id int (11) primary key auto_increment,
	name char(255),sex char(255),
	age int(11)
)charset utf8;
 
CREATE TABLE student_score
(
	id int (11) primary key auto_increment,
	class char(255),score char(255),
	student_id int(11)
)charset utf8;

（2）創(chuàng)建外鍵

ALTER TABLE student_score 
ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id);

（3）查看表結(jié)構(gòu)

SHOW CREATE TABLE student;
SHOW CREATE TABLE student_score;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(255) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` char(255) DEFAULT NULL,
  `score` char(255) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `s_id` (`student_id`),
  CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

三、驗證外鍵作用

1 先向主表中添加數(shù)據(jù)

再向從表中添加數(shù)據(jù)（從表中的外鍵已在主表中存在），正常運行。

#向 student 表中添加數(shù)據(jù)
INSERT INTO student (NAME, sex, age)
VALUES
	('小明', '男', '20');

#向 student_score 表中添加數(shù)據(jù)
INSERT INTO student_score (class, score, student_id)
VALUES
	('語文', '100', 1),
	('數(shù)學(xué)', '99', 1),
	('英語', '98', 1);

數(shù)據(jù)插入正常，無報錯、警告信息。

在這里插入圖片描述

2 觸發(fā)限制使用默認值 RESTRICT 的情況下

（1）從表插入新行，外鍵值不在主表中，被阻止

INSERT INTO student_score (class, score, student_id)
VALUES
	('語文', '100', 2);

ERROR 1452 (23000) : Cannot ADD
OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails (
	`tts`.`student_score`,
	CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)

（2）從表修改外鍵值，新值不是主表的主鍵值，阻止修改

UPDATE student_score
SET student_id = 2
WHERE
	student_id = 1;

ERROR 1452 (23000) : Cannot ADD
OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails (
	`tts`.`student_score`,
	CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)

（3）主表刪除行，其主鍵值在從表里存在便阻止刪除(要想刪除，必須先刪除從表的相關(guān)行)

DELETE FROM student WHERE id = 1;

ERROR 1451 (23000) : Cannot DELETE
OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails (
	`tts`.`student_score`,
	CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)

（4）主表修改主鍵值，舊值在從表里存在便阻止修改(要想修改，必須先刪除從表的相關(guān)行)

UPDATE student SET id = 2 WHERE id = 1;

ERROR 1451 (23000) : Cannot DELETE
OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails (
	`tts`.`student_score`,
	CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)

3 更改事件觸發(fā)限制為 CASCADE

#刪除舊的外鍵
ALTER TABLE student_score DROP FOREIGN KEY s_id;
 
#添加新的外鍵，修改事件觸發(fā)限制為 CASCADE
ALTER TABLE student_score 
ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id) 
ON DELETE CASCADE ON UPDATE CASCADE;

（1）查看表結(jié)構(gòu)

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` char(255) DEFAULT NULL,
  `score` char(255) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `s_id` (`student_id`),
  CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

（2）查看此時兩表中的數(shù)據(jù)

（3）此時，當(dāng)主表修改主鍵值，從表中相關(guān)行的外鍵值將一起修改

UPDATE student SET id = 2 WHERE id = 1;

（4）如果主表刪除行，從表中的相關(guān)行將一起被刪除

DELETE FROM student WHERE id = 2;

4 結(jié)論

事件觸發(fā)限制條件的不同，會造成兩張表中的操作限制不同，其他幾個限制條件相對好理解，大家可以自己進行嘗試，體會其中的區(qū)別。

四、刪除外鍵約束

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名;
 
ALTER TABLE student_score DROP FOREIGN KEY s_id;

總結(jié) 

到此這篇關(guān)于MySQL數(shù)據(jù)庫中外鍵(foreign key)的文章就介紹到這了,更多相關(guān)MySQL外鍵(foreign key)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • 101個MySQL的配置和優(yōu)化的提示

    101個MySQL的配置和優(yōu)化的提示

    這里是101條調(diào)節(jié)和優(yōu)化 MySQL安裝的技巧。一些技巧是針對特定的安裝環(huán)境的，但這些思路是通用的。我已經(jīng)把他們分成幾類，來幫助你掌握更多MySQL的調(diào)節(jié)和優(yōu)化技巧
    2013-08-08
  • mysql中的一些稍微復(fù)雜用法實例代碼

    mysql中的一些稍微復(fù)雜用法實例代碼

    這篇文章主要給大家介紹了關(guān)于mysql中的一些稍微復(fù)雜用法的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2018-11-11
  • Mysql自動備份與還原方法

    Mysql自動備份與還原方法

    Mysql自動備份與還原方法，需要的朋友可以參考下。
    2011-06-06
  • MySQL使用binlog日志恢復(fù)數(shù)據(jù)的方法步驟

    MySQL使用binlog日志恢復(fù)數(shù)據(jù)的方法步驟

    binlog日志是用于記錄所有修改數(shù)據(jù)庫內(nèi)容的操作,本文主要介紹了MySQL使用binlog日志恢復(fù)數(shù)據(jù)的方法步驟,具有一定的參考價值,感興趣的可以了解一下
    2025-03-03
  • 遠程訪問MySQL數(shù)據(jù)庫的方法小結(jié)

    遠程訪問MySQL數(shù)據(jù)庫的方法小結(jié)

    MySQL數(shù)據(jù)庫不允許從遠程訪問如何辦?本文提供了 3種解決思路方法
    2009-12-12
  • python中的mysql數(shù)據(jù)庫LIKE操作符詳解

    python中的mysql數(shù)據(jù)庫LIKE操作符詳解

    LIKE操作符用于在WHERE子句中搜索列中的指定模式，like操作符的語法在文章開頭也給大家提到，通過兩種示例代碼給大家介紹python中的mysql數(shù)據(jù)庫LIKE操作符知識，感興趣的朋友跟隨小編一起看看吧
    2021-07-07
  • MySQL 事務(wù)autocommit自動提交操作

    MySQL 事務(wù)autocommit自動提交操作

    這篇文章主要介紹了MySQL 事務(wù)autocommit自動提交操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • 關(guān)于mysql時間區(qū)間問題淺析

    關(guān)于mysql時間區(qū)間問題淺析

    在很多地方都使用到了mysql的日期查詢,下面這篇文章主要給大家介紹了關(guān)于mysql時間區(qū)間問題的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
    2022-04-04
  • mysql啟動服務(wù)報1058錯誤的解決方法

    mysql啟動服務(wù)報1058錯誤的解決方法

    這篇文章主要介紹了mysql啟動服務(wù)報1058錯誤的解決方法,需要的朋友可以參考下
    2014-03-03
  • MYSQL ON UPDATE CURRENT_TIMESTAMP當(dāng)字段值發(fā)生改變時才會更新記錄的時間

    MYSQL ON UPDATE CURRENT_TIMESTAMP當(dāng)字段值發(fā)生改變時才會更

    本文主要介紹了MYSQL ON UPDATE CURRENT_TIMESTAMP當(dāng)字段值發(fā)生改變時才會更新記錄的時間，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-01-01

最新評論