创建两个表:
表news:
-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
content VARCHAR(100) NOT NULL COMMENT '新闻内容',
cateId TINYINT NOT NULL COMMENT '新闻所属分类编号'
)ENGINE=INNODB;
表news_cate:
-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
cateName VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称',
cateDesc VARCHAR(100) NOT NULL DEFAULT '' COMMENT '分类描述'
)ENGINE=INNODB;
1. 只有InnoDB存储引擎支持外键。
2. 创建外键
2.1. 建表时指定外键
2.1.1. [CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
在表news和表news_cate中,表news_cate为主表,在news中指定外键,重新创建表news:
-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
content VARCHAR(100) NOT NULL COMMENT '新闻内容',
cateId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类编号',
FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;
2.1.2. 子表的外键字段和主表的主键字段类型要相似;如果是数值型要求一致,并且无符号也要一致;如果是字符型要求类型一致,长度可以不同。
2.1.3 如果外键字段没有创建索引,MySQL会自动帮我们添加索引
2.1.4 子表的外键关联的必须是父表的主键
2.1.5 测试删除(或更新)主表中的记录及删除主表
(1)若子表中关联了主表中的某条记录,则会删除(或更新)失败,如删除主表news_cate中id=1的记录,子表news如下:
-- 测试删除(或更新)主表中的记录及删除主表
DELETE FROM news_cate WHERE id = 1;
结果:
(2)要想删除(或更新)主表中的某条记录,情况一:需删除(或更新)记录为 子表未关联主表中的某条记录
INSERT news_cate(cateName) VALUES('教育新闻');
UPDATE news_cate SET cateName = '教育新闻' WHERE id = 5;
(3)情况二:需删除(或更新)记录为 子表关联主表中的某条记录,须先删除(或更新)子表中相关的记录,再删除(或更新)主表中的记录
DELETE FROM news WHERE id = 4;
DELETE FROM news_cate WHERE id = 2;
(4)要想删除主表必须先删除关联的子表
DROP TABLE news;
DROP TABLE news_cate;
2.1.6 添加外键名称
CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
2.1.7 外键约束的参照操作
(1)CASCADE : 从主表删除或更新,子表也跟着删除或更新,级联的操作
(2)SET NULL :从主表删除或更新,并设置子表的外键列为NULL
(3)NO ACTION | RESTRICT :拒绝对主表做更新或删除操作
2.2. 动态添加外键
2.2.1 动态添加外键ALTER TABLE 表名 ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主表字段);
注意:动态添加外键之前表中的记录一定是合法的记录,没有脏值,否则外键添加不成功。
结合2.1.7中的CASCADE测试,此时便可以直接删除或更新主表中的记录,若与子表关联,则子表和主表一起被删除或更新:
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;
2.2.2 动态删除外键 ALTER TABLE 表名 DROP FOREIGN KEY fk_name;