MySQL外鍵約束的特點(diǎn)和外鍵約束下的多表操作

什么是外鍵約束？

MySQL 外鍵約束(FOREIGN KEY)是表的一個特殊字段，經(jīng)常與主鍵約束一起使用。對于兩個具有關(guān)聯(lián)關(guān)系的表而言，相關(guān)聯(lián)字段中主鍵所在的表就是主表(父表)，外鍵所在的表就是從表(子表)。

外鍵用來建立主表與從表的關(guān)聯(lián)關(guān)系，為兩個表的數(shù)據(jù)建立連接，約束兩個表中數(shù)據(jù)的一致性和完整性。比如，一個水果攤，只有蘋果、桃子、李子、西瓜等 4 種水果，那么，你來到水果攤要買水果就只能選擇蘋果、桃子、李子和西瓜，其它的水果都是不能購買的。

外鍵約束的特點(diǎn)

定義一個外鍵時，需要遵守下列規(guī)則：

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

必須為主表定義主鍵。

主鍵不能包含空值，但允許在外鍵中出現(xiàn)空值。也就是說，只要外鍵的每個非空值出現(xiàn)在指定的主鍵中，這 個外鍵的內(nèi)容就是正確的。

在主表的表名后面指定列名或列名的組合。這個列或列的組合必須是主表的主鍵或候選鍵。

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

外鍵中列的數(shù)據(jù)類型必須和主表主鍵中對應(yīng)列的數(shù)據(jù)類型相同。

創(chuàng)建外鍵約束

方式1-在創(chuàng)建表時設(shè)置外鍵約束

在 create table 語句中，通過 foreign key 關(guān)鍵字來指定外鍵，具體的語法格式如下：

[constraint <外鍵名>] foreign key 字段名 [，字段名2，…] references <主表名> 主鍵列1 [，主鍵列2，…]

實(shí)現(xiàn)

create database mydb3; 
use mydb3;
-- 創(chuàng)建部門表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);

方式2-在創(chuàng)建表時設(shè)置外鍵約束

外鍵約束也可以在修改表時添加，但是添加外鍵約束的前提是：從表中外鍵列中的數(shù)據(jù)必須與主表中主鍵列中的數(shù)據(jù)一致或者是沒有數(shù)據(jù)。創(chuàng)建表時設(shè)置外鍵約束的語法格式如下：

alter table <數(shù)據(jù)表名> add constraint <外鍵名> foreign key(<列名>) references 
<主表名> (<列名>);

實(shí)現(xiàn)

-- 創(chuàng)建部門表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);
-- 創(chuàng)建員工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 員工編號
  ename varchar(20), -- 員工名字
  age int,  -- 員工年齡
  dept_id varchar(20)  -- 員工所屬部門
 
);
-- 創(chuàng)建外鍵約束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

在外鍵約束下的數(shù)據(jù)操作

1、數(shù)據(jù)插入

 --添加主表數(shù)據(jù)
 -- 注意必須先給主表添加數(shù)據(jù)
insert into dept values('1001','研發(fā)部');
insert into dept values('1002','銷售部');
insert into dept values('1003','財務(wù)部');
insert into dept values('1004','人事部’);

--添加從表數(shù)據(jù)
  -- 注意給從表添加數(shù)據(jù)時，外鍵列的值不能隨便寫，必須依賴主表的主鍵列
insert into emp values('1','喬峰',20, '1001');
insert into emp values('2','段譽(yù)',21, '1001');
insert into emp values('3','虛竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','掃地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鳩摩智',50, '1003'); 
insert into emp values('8','天山童姥',60, '1005');  -- 不可以

2、刪除數(shù)據(jù)

--刪除數(shù)據(jù)
 /*
   注意：
       1：主表的數(shù)據(jù)被從表依賴時，不能刪除，否則可以刪除
       2: 從表的數(shù)據(jù)可以隨便刪除
 */
delete from dept where deptno = '1001'; -- 不可以刪除
delete from dept where deptno = '1004'; -- 可以刪除
delete from emp where eid = '7'; -- 可以刪除

刪除外鍵約束

當(dāng)一個表中不需要外鍵約束時，就需要從表中將其刪除。外鍵一旦刪除，就會解除主表和從表間的關(guān)聯(lián)關(guān)系。

格式如下：

alter table <表名> drop foreign key <外鍵約束名>;

實(shí)現(xiàn)：

alter table emp2 drop foreign key dept_id_fk;