MySQL中外键(FROEIGN KEY)的使用

MySQL中外键(FROEIGN KEY)的使用

1.定义：

外键用来在两个表的数据之间建立链接，它可以是一列或者多列；一个表可以有一个或多个外键。

外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键是表的一个字段，不是表的主键，但对应另一个表的主键；定义外键后，不允许删除另一个表中具有关联关系的行。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表；

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

被指向的实体，称之为主实体（主表），也叫父实体（父表）

负责指向的实体，称之为从实体（从表），也叫子实体（子表）；

若‘表1’的一个列既是主键又是外键，而这个外键约束又对应另一个表‘表2’的主键，那么‘表1’和‘表2’就可以合并起来了

2.作用：

外键的主要作用是保持数据的一致性、完整性。

如果主表没有相关的记录，从表不能插入；

更新时，不能改为主键表中没有的值；

删除主键表记录时，可以在建外键时选定外键记录一起级联删除还是拒绝删除；同样有级联更新和拒绝更新的选择。

ON DELETE CASCADE 级联删除

ON UPDATE CASCADE 级联更新

3.设置MySQL外键约束字段的规则：

Ø 父表必须已经存在于数据库中，或者是当前正在创建的表；若是后者，则父表与子表是同一个表，这样的表称为自参照完整性。

Ø 必须为父键定义主键。

Ø 主键不能包含空值，但允许在外键中出现空值；也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

Ø 在父表的表名后面指定列名或列名的组合，这个列的组合必须是父键的主键或者候选键。

Ø 外键中列的数据类型和父键主键中对应的数据类型相同。

Ø 外键中列的数目必须和父键中列的数目相同。

4.创建、添加外键的语法规则：

(1) 创建

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]

REFERENCES <主表名> 主键列1 [，主键列2，…]

“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；

“字段名”表示子表需要添加外键约束的字段列；

“主表名”即被子表外键所依赖的表的名称；

“主键列”表示主表中定义的主键列，或者列组合。

(2) 添加

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> (形如：FK_从表_主表)

FOREIGN KEY (<列名>) REFERENCES <主表名> (<列名>);

实例1：在test_db数据库中创建employee表和dept表，表结构如下：

Employee表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
e_no	员工编号	INT(11)	是	否	是	是	否
e_name	员工姓名	VARCHAR(50)	否	否	是	否	否
e_gender	员工性别	CHAR(2)	否	否	否	否	否
dept_no	部门编号	INT(11)	否	是	是	否	否
e_job	职位	VARCHAR(50)	否	否	是	否	否
e_salary	薪水	INT(11)	否	否	是	否	否
hiredate	入职日期	DATE	否	否	是	否	否

Dept表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
d_no	部门编号	INT(11)	是	是	是	是	是
d_name	部门名称	VARCHAR(50)	否	否	是	否	否
d_location	部门地址	VARCHAR(100)	否	否	否	否	否

创建数据表dept的SQL语句：

mysql> create table dept
  -> (
  -> d_no int(11) primary key not null unique auto_increment,
  -> d_name varchar(50) not null,
  -> d_location varchar(100)
  -> );
Query OK, 0 rows affected (0.01 sec)

创建数据表employee，并在表employee上创建外键约束，让它的键dept_no作为外键关联到表dept的主键d_no，SQL语句如下：

mysql> create table employee
  -> (
  -> e_no int(11) primary key not null unique,
  -> e_name varchar(5) not null,
  -> e_gender char(2),
  -> dept_no int (11) not null,
  -> e_job varchar(50) not null,
  -> e_salary int(11) not null,
  -> hireDate date not null,
  -> constraint fk_dept_employee
  -> foreign key(dept_no) references dept(d_no)
-> );
Query OK, 0 rows affected (0.01 sec)

以上语句执行之后，在表employee上添加了名称为fk_dept_employee的外键约束,

外键名称为dept_no，其依赖于表dept的主键d_no

#查看表结构：
mysql> show create table employee\G

*************************** 1. row ***************************
     Table: employee
Create Table: CREATE TABLE `employee` (
 `e_no` int(11) NOT NULL,
 `e_name` varchar(5) NOT NULL,
 `e_gender` char(2) DEFAULT NULL,
 `dept_no` int(11) NOT NULL,
 `e_job` varchar(50) NOT NULL,
 `e_salary` int(11) NOT NULL,
 `hireDate` date NOT NULL,
 PRIMARY KEY (`e_no`),
 UNIQUE KEY `e_no` (`e_no`),
 KEY `fk_dept_employee` (`dept_no`),
 CONSTRAINT `fk_dept_employee` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`d_no
`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
/*

关联指的是在关系数据型数据库中，相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父键的主键，且关键字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误” ERROR 1005 (HY000): Can't create table 'database.tablename' (errno: 150)”。

mysql> alter table employee
  -> add constraint fk_dept_employee
  -> foreign key(dept_no)
  -> references dept(d_no);
ERROR 1005 (HY000): Can't create table 'test_db.#sql-888_1' (errno: 150)
*/

实例2：在数据库’test_db1’中创建数据表employee和dept，修改数据表 employee，将字段dept_no设置为外键，与数据表dept的主键d_no进行关联，SQL语句如下；

mysql> create table employee
  -> (
  -> e_no int(11) primary key not null unique,
  -> e_name varchar(50) not null,
  -> e_gender char(2),
  -> dept_no int(11) not null,
  -> e_job varchar(50) not null,
  -> e_salary int(11) not null,
  -> hiredate date not null
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table dept
  -> (
  -> d_no int(11) primary key  not null unique auto_increment,
  -> d_name varchar(50) not null,
  -> d_location varchar(100)
  -> );
Query OK, 0 rows affected (0.01 sec)

#添加外键约束：

mysql> alter table employee
  -> add constraint fk_dept_employee
  -> foreign key(dept_no)
  -> references dept(d_no);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看表结构：

mysql> show create table employee\G
*************************** 1. row ***************************
    Table: employee
Create Table: CREATE TABLE `employee` (
 `e_no` int(11) NOT NULL,
 `e_name` varchar(50) NOT NULL,
 `e_gender` char(2) DEFAULT NULL,
 `dept_no` int(11) NOT NULL,
 `e_job` varchar(50) NOT NULL,
 `e_salary` int(11) NOT NULL,
 `hiredate` date NOT NULL,
 PRIMARY KEY (`e_no`),
 UNIQUE KEY `e_no` (`e_no`),
 KEY `fk_dept_employee` (`dept_no`),
 CONSTRAINT `fk_dept_employee` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`d_no
`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

5.删除外键：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

（外键一旦删除，就会解除主表和从表间的关联关系）

mysql> alter table employee drop foreign key fk_dept_employee;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employee\G;
*************************** 1. row ***************************
    Table: employee
Create Table: CREATE TABLE `employee` (
 `e_no` int(11) NOT NULL,
 `e_name` varchar(5) NOT NULL,
 `e_gender` char(2) DEFAULT NULL,
 `dept_no` int(11) NOT NULL,
 `e_job` varchar(50) NOT NULL,
 `e_salary` int(11) NOT NULL,
 `hireDate` date NOT NULL,
 PRIMARY KEY (`e_no`),
 UNIQUE KEY `e_no` (`e_no`),
 KEY `fk_dept_employee` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

6.外键约束的三种形式：

(1) district：严格模式(默认)，父表不能删除或更新一个已经被子表数据引用的记录。

mysql> delete from departments where d_no=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`yggl`.`employee`, CONSTRAINT `fk_dept_emp` FOREIGN KEY (`dept_no`) RE
FERENCES `departments` (`d_no`))

(2) cascade：级联模式，父键的操作，对应子表关联的数据也跟着操作。

(3) set null：置空模式，父表被操作之后，子表对应的外键字段被置空。

通常情况下，合理的模式应该是这样：删除父表中的数据，子表置空；更新父表的时候，子表做级联操作。(foreign key(外键字段) references 父键(外键字段) on delete set null on update cascade)

指定模式的语法：

foreign key(外键字段) references 父表(外键字段) on delete 模式 on update 模式