MySQL多表操作的外來鍵約束教學

2023-02-01 18:02:13

一.多表關係

MySQL多表之間的關係可以概括為:一對一、一對多/多對一關係，多對多

一對一關係

一個學生只有一張身份證;一張身份證只能對應一學生。
在任一表中新增唯─外來鍵，指向另一方主鍵，確保一對一關係。
一般一對一關係很少見，遇到一對一關係的表最好是合併表。

一對多/多對一關係

部門和員工
分析:一個部門有多個員工，一個員工只能對應一個部門實現原則:在多的一方建立外來鍵,指向一的一方的主鍵

多對多關係

學生和課程
分析:一個學生可以選擇很多門課程，一個課程也可以被很多學生選擇
原則:多對多關係實現需要藉助第三張中間表。中間表至少包含兩個欄位，將多對多的關係，拆成一對多的關係，中間表至少要有兩個外來鍵，這兩個外來鍵分別指向原來的那兩張表的主鍵

二.外來鍵約束

MySQL外來鍵約束（FOREIGN KEY)是表的一個特殊欄位，經常與主鍵約束一起使用。對於兩個具有關聯關係的表而言，相關聯欄位中主鍵所在的表就是主表(父表)，外來鍵所在的表就是從表(子表)。
外來鍵用來建立主表與從表的關聯關係，為兩個表的資料建立連線，約束兩個表中資料的一致性和完整性。比如，一個水果攤，只有蘋果、桃子、李子、西瓜等4種水果，那麼，你來到水果攤要買水果就只能選擇蘋果、桃子、李子和西瓜，其它的水果都是不能購買的。

特點

定義一個外來鍵時，需要遵守下列規則:

主表必須已經存在於資料庫中，或者是當前正在建立的表。
必須為主表定義主鍵。
主鍵不能包含空值，但允許在外來鍵中出現空值。也就是說，只要外來鍵的每個非空值出現在指定的主鍵中，這個外來鍵的內容就是正確的。
在主表的表名後面指定列名或列名的組合。這個列或列的組合必須是主表的主鍵或候選鍵。
外來鍵中列的數目必須和主表的主鍵中列的數目相同。
外來鍵中列的資料型別必須和主表主鍵中對應列的資料型別相同。

建立外來鍵約束

方式1-在建立表時設定外來鍵約束

在create table語句中，通過foreign key關鍵字來指定外來鍵，具體的語法格式如下:

use world;
-- 建立部門表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);
 
-- 建立員工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 員工編號
  ename varchar(20), -- 員工名字
  age int,  -- 員工年齡
  dept_id varchar(20),  -- 員工所屬部門
  constraint emp2_fk foreign key (dept_id) references dept(deptno)
);

方式2-在建立表時設定外來鍵約束

外來鍵約束也可以在修改表時新增，但是新增外來鍵約束的前提是:從表中外來鍵列中的資料必須與主表中主鍵列中的資料一致或者是沒有資料。

use world;
-- 建立部門表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);
 
-- 建立員工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 員工編號
  ename varchar(20), -- 員工名字
  age int,  -- 員工年齡
  dept_id varchar(20),  -- 員工所屬部門
  constraint emp2_fk foreign key (dept_id) references dept(deptno)
);