外键(foreign key),来自外表的键,一般是一张表的某个字段来自其他表的主键,这样称该字段为外键 ;
增加外键
创建外键有2两种方法:
-
创建表的时候创建
# 在所有的表字段以后,增加外键 语法:[constraint 外键名字] foreign key(外键字段)references 外表 ; mysql> create table student2( -> id int primary key, -> name varchar(10) not null, -> c_id int not null, -> -- 添加外键 -> constraint c_id_fk foreign key(c_id) references class(id) -> )charset utf8 ; Query OK, 0 rows affected -- 查看发现,c_id 不是 foreign key 是个 MUL mysql> desc student2 ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | | | | name | varchar(10) | NO | | | | | c_id | int(11) | NO | MUL | | | +-------+-------------+------+-----+---------+-------+ 3 rows in set -- 查看建表语句 mysql> show create table student2 ; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student2 | CREATE TABLE `student2` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL, `c_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `c_id_fk` (`c_id`), -- 多一个索引 -- 下一行 还有一个外键索引 CONSTRAINT `c_id_fk` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
为啥外键字段
key
栏,不是foreign key
而是MUL
?因为创建外键有个前提,字段必须是索引,如果不是则自动帮我们创建一g个,就是上面的那个 KEY
c_id_fk
(c_id
), 所以c_id
字段身上的索引不止一个,所有key
栏,就写上MUL
; -
创建表之后再增加
语法:alter table 表名 add [constraint 外键名] foreign key(外键字段) references 父表(主键字段) ; mysql> create table student2( -> id int primary key auto_increment , -> name varchar(10) not null, -> c_id int not null, -> sex tinyint -> )charset utf8 ; Query OK, 0 rows affected mysql> desc student2 ; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | | | | c_id | int(11) | NO | | | | | sex | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set -- 添加外键 mysql> alter table student2 add constraint c_id_fk foreign key(c_id) references class(id) ; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student2 ; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | | | | c_id | int(11) | NO | MUL | | | | sex | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set
删除外键&&修改外键
外键允许有多个!
但是外键不可修改,只能先删除再新增 ;
# 删除外键
语法:alter table 表名 drop foreign key 外键名 ;
--查看表结构
mysql> desc student2 ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | | |
| c_id | int(11) | NO | MUL | | |
| sex | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set
-- 删除外键
mysql> alter table student2 drop foreign key c_id_fk ;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表结构,还是 MUL
mysql> desc student2 ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | | |
| c_id | int(11) | NO | MUL | | |
| sex | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set
-- 查看建表语句
mysql> show create table student2 ;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student2 | CREATE TABLE `student2` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(10) NOT NULL,
`c_id` int(11) NOT NULL,
`sex` tinyint(4) default NULL,
PRIMARY KEY (`id`),
# 自动创建的索引还在
KEY `c_id_fk` (`c_id`)
# 创建外键的语句没了
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
删除外键以后,从表结构上是看不出来的,需要看建表语句 ;
外键作用
-
约束子表操作
子表数据进行写操作(增和改)的时候,如果要写入的数据,在对应的外键字段所在的父表找不到对应的匹配,则写入失败 ;
-
约束父表操作
父表进行写操作(删和改)的时候,涉及到主键的时候,如果对应的主键,已经在子表中被使用,那么操作就会失败,不允许进行这样的操作 ;
外键条件
-
外键要存在
父表的主键字段必须存在,否则引用啥呢。
-
外键字段类型必须和父表字段类型一致
属性也要一致,
unsigned
,就都是unsigned
否则,还是会出现数据不匹配问题; -
表的存储引擎必须是
innodb
它是默认的存储引擎,只要你没改过表选项,那么默认就是它;如果不是
innodb
存储引擎,那么外键虽然可以创建成功,但是没有约束效果 ; -
同一张表的外键名字,不可以重复
删除外键的时候,是根据名字删除的,假如可以重复,那么就无法确定删除谁了;
-
表已经创建完成以后为字段添加外键约束
如果该字段已经有数据了,那么必须保证数据与父表的主键相对应 ;
约束模式
有三种,但是都是对父表的约束 ;
-
district
严格模式(默认)父表不能删除或者更新一个已经被子表数据引用的主键 ;
-
cascade
级联模式父表的操作,会被同等的操作到子表上;比如,父表更新主键,则子表对应的字段值也被更新 ;
-
set null
置空模式父表操作以后,子表对应的数据被置为
NULL
;
通常合理的做法是:
删除的时候子表置空,更新的时候子表级联操作 ;
# 指定模式语法
foreign key(字段列表) references 父表(主键) on delete 模式 on update 模式 ;
删除置空的前提条件,外键字段允许为空,如果不满足条件,外键无法创建 ;
我还年轻,我有什么事做不成呢 … ℒℴѵℯ ℒℴѵℯ ℒℴѵℯ