mysql外键

最新推荐文章于 2024-08-06 15:46:06 发布

海尼尔

最新推荐文章于 2024-08-06 15:46:06 发布

阅读量621

点赞数

分类专栏： MySQL 文章标签： mysql 索引

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了MySQL中外键的概念、使用方法及其对数据完整性的维护作用。包括外键的定义语法、不同事件触发限制选项（如CASCADE、RESTRICT等），并通过具体示例展示了如何创建带有外键的表以及执行相关操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、基本概念

--Mysql 键==索引（外键、主键）主键自动索引，外键-参照表中索引，innodb不能自动创建索引

--外键：一对一，一对多（表与表）

--只为性能，不完整性检查->MyISAM 完整性+性能->InnoDB

--外键条件：两表都是InnoDB，外键列必须建立索引，两表外键列必须数据类型相似

2、使用方法

外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
① RESTRICT（限制外表中的外键改动，默认值）
② CASCADE（跟随外键改动）
③ SET NULL（设空值）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的）

3、示例
创建表1
create table repo_table(
repo_id char(13) not null primary key,
repo_name char(14) not null)
type=innodb;
创建表2
mysql> create table busi_table(
busi_id char(13) not null primary key,
busi_name char(13) not null,
repo_id char(13) not null,
foreign key(repo_id) references repo_table(repo_id))
type=innodb;
插入数据
insert into repo_table values("12","sz"); //success
insert into repo_table values("13","cd"); //success
insert into busi_table values("1003","cd", "13"); //success
insert into busi_table values("1002","sz", "12"); //success
insert into busi_table values("1001","gx", "11"); //failed,提示：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`))
增加级联操作
mysql> alter table busi_table
add constraint id_check
foreign key(repo_id)
references repo_table(repo_id)
on delete cascade
on update cascade;
ENGINE=InnoDB DEFAULT CHARSET=utf8 //另一种方法，可以替换type=innodb;
4、相关操作
外键约束（表2）对父表（表1）的含义:
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。
关键字-含义
CASCADE
删除包含与已删除键值有参照关系的所有记录
SET NULL
修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT
拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
NO ACTION
啥也不做
5、其他
在外键上建立索引:
index repo_id (repo_id),
foreign key(repo_id) references repo_table(repo_id))

6、我的例子

Mysql-SQL语句例子：

create table ssh_department(
id int(10),
department_name varchar(50),
constraint dept_FK primary key(id)
);
create table ssh_employee(
id int(10),
dept_id int(10),
last_name varchar(50),
email varchar(50),
birth date,
create_time date,
constraint emp_PK primary key(id),
constraint emp_FK foreign key(dept_id) references ssh_department(id)
);
insert into ssh_department values(1,'销售部');
insert into ssh_department values(2,'信息部');
insert into ssh_department values(3,'人事部');
insert into ssh_department values(4,'财务部');
insert into ssh_department values(5,'信贷部');
insert into ssh_department values(6,'后勤部');

insert into ssh_employee values(1,1,'张三','zs@163.com','1989-02-15','2015-03-31');
insert into ssh_employee values(2,3,'李四','ls@163.com','1990-07-23','2015-03-31');
insert into ssh_employee values(3,4,'王五','ww@163.com','1988-05-02','2015-03-31');
insert into ssh_employee values(4,2,'赵六','zl@163.com','1990-09-30','2015-03-31');
insert into ssh_employee values(5,1,'周七','zq@163.com','1989-08-19','2015-03-31');