外键约束 -> 只有InnoDBDE的存储引擎支持外键
使用外键的意义
先创建两个表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE news_cate( id TINYINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(50) NOT NULL UNIQUE, cateDesc VARCHAR(100) NOT NULL DEFAULT '' )CHARSET=UTF8; -- 新闻表 news CREATE TABLE news( id INT UNSIGNED AUTO_INCREMENT KEY, title VARCHAR(100) NOT NULL UNIQUE, content VARCHAR(1000) NOT NULL, cateId TINYINT UNSIGNED NOT NULL )CHARSET=UTF8; INSERT news_cate(cateName) VALUES('国内新闻'), ('国际新闻'), ('娱乐新闻'), ('体育新闻'); INSERT news(title,content,cateId) VALUES('a1','aaaa1',1), ('a2','aaaa2',1), ('a3','aaaa3',4), ('a4','aaaa4',2), ('a5','aaaa5',3); |
联查一下:
|
1 2 3 4 5 |
-- news_cate cateName SELECT n.id,n.title,n.content,c.cateName FROM news AS n JOIN news_cate AS c ON n.cateId=c.id; |
假如现在删掉了国际新闻：
|
1 2 |
DELETE FROM news_cate WHERE id=2; SELECT * FROM news; |
这个时候我们删掉了国际新闻吗，可以用户还是关注了国际新闻，显然这样是不合适的。
或者写入了一个非法的数据，没有这个分类，发布的时候都没有这个分类是不应该成功的，然而我们的语句并没有什么问题。
甚至把新闻的 表删了，新闻还在分类下，因此为了保证数据的一致性和完整性我们要使用外键来确保数据的正确性 。
创建外键
1.建表时指定外键
|
1 |
[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称) |
外键首先需要有一个主表，在创建外键的时候就在子表中参照主表中的主键来创建。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 新闻分类表 news_cate 主表 CREATE TABLE news_cate( id TINYINT UNSIGNED AUTO_INCREMENT KEY, cateName VARCHAR(50) NOT NULL UNIQUE, cateDesc VARCHAR(100) NOT NULL DEFAULT '' )ENGINE=INNODB,CHARSET=UTF8; -- 新闻表 news 从表 CREATE TABLE news( id INT UNSIGNED AUTO_INCREMENT KEY, title VARCHAR(100) NOT NULL UNIQUE, content VARCHAR(1000) NOT NULL, cateId TINYINT UNSIGNED NOT NULL, FOREIGN KEY(cateId) REFERENCES news_cate(id) )ENGINE=INNODB,CHARSET=UTF8; |
- 子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致，如果是字符串类型，长度可以不一致但是类型也要是一致的。
- 如果外键字段没有创建索引MySQL会自动添加索引
- 指标的外键关联的必须是父表的主键
外键约束的参照操作：
- CASCADE：从父表删除或更新，子表也跟着删除或者更新，级联的操作。
- SET NULL：从父表删除或者更新记录，并设置子表的外键列为NULL。
- NO ACTION | RESTRICT：拒绝对父表做更新或者删除操作
|
1 2 3 4 5 |
-- 指定级联操作 DELETE CASCADE UPDATE CASCADE ALTER TABLE news ADD FOREIGN KEY(cateId) REFERENCES news_cate(id) ON DELETE CASCADE ON UPDATE CASCADE; |
测试非法记录
|
1 |
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8); |
测试删除父表中的记录、删除父表
|
1 2 3 |
DELETE FROM news_cate WHERE id=1; UPDATE news_cate SET id=10 WHERE id=1; |
插入、修改数据是可以成功的，对子表没有影响的是没有关系的。删除没有关联项目的外键也是没有关系的。但是删除记录、删除表如果有关联的话也是无法执行的，如果要删除需要先删除主表再删除父表。
|
1 |
INSERT news_cate(cateName) VALUES('教育新闻'); |
外键动态增删
动态添加外键
|
1 2 |
ALTER TABLE tbl_name [CONSTRAINT 外键名称] ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段); |
动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功
动态删除外键
|
1 2 |
ALTER TABLE tbl_name DROP FOREIGN KEY fk_name; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 删除外键 ALTER TABLE news DROP FOREIGN KEY cateId_fk_newsCate; -- 添加外键 ALTER TABLE news ADD FOREIGN KEY(cateId) REFERENCES news_cate(id); -- 删除外键 ALTER TABLE news DROP FOREIGN KEY news_ibfk_1; ALTER TABLE news ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id); |
