外键的详细讲解
使用条件
① 两个表必须是InnoDB表,MyISAM表暂时不支持外键
② 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显式建立;
③ 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以
使用方法
外键的定义语法:
[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(无动作,默认的)
实列
Crete table if not exists table1(
t1_id char(12) NOT NULL PRIMARY KEY,
t1_name char(12) NOT NULL
type=innodb
)
create table if not exist table2(
t2_id char(12) not null primary key
t2_name char(12) not null
t1_id char(12) not null
foreign key(t1_id) references t1_table(t1_id)
)
2)插入数据
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
))
此时table1表不能进行dMl操作呢 table2 表不能插入数据一定要与table1相关/
3)增加级联操作
mysql> alter table busi_table
-> add constraint id_check
-> foreign key(repo_id)
-> references repo_table(repo_id)
-> on delete cascade
-> on update cascade;
3、相关操作
外键约束(表2)对父表(表1)的含义:
此时table1表能进行dMl操作呢 随之table2表中的数据也随之table1进行相关关联的数据进行操作 table2 表不能插入数据一定要与table1相关/
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句。
关键字
CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录,使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT 拒绝删除要求,直到使用删除键值的辅助表被手工删除,并且没有参照时(这是默认设置,也是最安全的设置)
NO ACTION 啥也不做
4、其他
在外键上建立索引:
index repo_id (repo_id),
foreign key(repo_id) references repo_table(repo_id))
摘要: 外键具有保持数据完整性和一致性的机制,目前MySQL只在InnoDB引擎下支持,下面实例下一个小操作来说明下外键的关联操作,用来保持数据的完整性和一致性。