外键约束
 
只有InnoDB存储引擎支持外键，并且在创建外键的时候，我们子表的外键必须关联主表的主键。
外键字段依赖于已经存在的表的一个主键，所以一定要先有主表。
我们的外键字段必须和我们主表的主键字段的数据类型要相似：如果是数值型，两者必须类型一致，并且同时为有无符号；如果是字符串型，两者的长度可以不同，但是类型必须相同。
如果子表的外键字段没有创建索引，但是当我们创建完外键之后，它会自动的添加索引。
如果子表中有记录外键关联着主表中的记录，那么主表中的这条记录以及主表是无法删除的，子表中的这些记录可以删除。
 
创建外键
 
建表时指定外键
 
[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
 
外键名称一般以 从表字段名称_fk_主表名称 进行命名
 
动态删除外键
 
ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;
 
动态增加外键
 
ALTER TABLE tbl_name
ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
 
注意：动态添加外键之前表中的记录一定是合法的记录，没有脏值，否则外键添加不成功。
 
外键约束的参照操作
 
CASCADE
 
级联的。当我们对父表进行删除或者更新，那么我们子表对应得也会进行删除或者是更新。
 
SET NULL
 
从父表删除或者更新记录，会设置子表对应的外键列为NULL。但是得确保这个字段是可以为空得，不然无法写入NULL值。
 
NO ACTION | RESTRICT
 
这是默认的。拒绝对父表做更新或删除操作
 
测试
 
建表时指定外键测试
 
-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);
INSERT news_cate(cateName)
VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号',
    FOREIGN KEY(cateId) REFERENCES news_cate(id)
);
INSERT news(title,content,cateId)
VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);
 
测试插入非法记录
 
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
 
1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
 
也就会报错了
 
测试删除父表中的记录和删除父表
 
-- 测试删除父表中的记录
DELETE FROM news_cate WHERE id=1;
 
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
 
-- 测试删除父表
DROP TABLE IF EXISTS news_cate;
 
3730 - Cannot drop table 'news_cate' referenced by a foreign key constraint 'news_ibfk_1' on table 'news'.
 
测试更新父表中的记录
 
UPDATE news_cate SET id=10 WHERE id=1;
 
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
 
尝试向父类插入记录
 
INSERT news_cate(cateName) VALUES('教育新闻');
 
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  1 | 国内新闻 | 无       |
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
|  5 | 教育新闻 | 无       |
+----+----------+----------+
 
这条语句是可以成功的
 
尝试更改新插入的记录
 
这条记录是刚插入的，也就是子表还没有使用的。
 
UPDATE news_cate SET cateName='教育' WHERE id=5;
 
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  1 | 国内新闻 | 无       |
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
|  5 | 教育     | 无       |
+----+----------+----------+
 
尝试在建表时指定外键并指定外键名称
 
-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号',
    CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
);
 
下面时表的信息，我们就可以看到我们命名的外键名称
 
| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`),
  CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
尝试动态删除外键
 
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;
 
查看表信息，我们可以发现之前的外键被删除了
 
| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
尝试动态添加外键
 
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
 
查看表信息
 
| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId` (`cateId`),
  CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
在动态添加外键的时候指定外键名称
 
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
 
查看表信息
 
| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`),
  CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
尝试在表中有非法记录时动态插入外键
 
-- 插入非法记录
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
 
-- 尝试动态添加外键
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

-- 会报错
1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`#sql-3d84_4b`, CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))
 
我们将那个非法的记录删除后再进行动态添加外键
 
DELETE FROM news WHERE id=6;

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
 
添加成功
 
所以在表中存在对于将要添加的外键来说是非法的记录时，将无法添加这个外键。
 
尝试指定级联操作
 
-- 指定级联操作 DELETE,UPDATE CASCADE
-- 注意先把之前同名的外键删除
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;
 
-- 尝试更新父表news_cate
UPDATE news_cate SET id=11 WHERE id=1;
 
我们可以发现上面的操作中，我们仅仅对父表进行了更新，但是父表和子表同时进行了更新
 
SELECT * FROM news_cate;
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
| 11 | 国内新闻 | 无       |
+----+----------+----------+

SELECT * FROM news;
+----+-------+---------+--------+
| id | title | content | cateId |
+----+-------+---------+--------+
|  1 | a1    | aaaa1   |     11 |
|  2 | a2    | aaaa2   |     11 |
|  3 | a3    | aaaa3   |      4 |
|  4 | a4    | aaaa4   |      2 |
|  5 | a5    | aaaa5   |      3 |
+----+-------+---------+--------+
 
-- 尝试删除父表中的记录
DELETE FROM news_cate WHERE id=2;
 
SELECT * FROM news_cate;
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
| 11 | 国内新闻 | 无       |
+----+----------+----------+

SELECT * FROM news;
+----+-------+---------+--------+
| id | title | content | cateId |
+----+-------+---------+--------+
|  1 | a1    | aaaa1   |     11 |
|  2 | a2    | aaaa2   |     11 |
|  3 | a3    | aaaa3   |      4 |
|  5 | a5    | aaaa5   |      3 |
+----+-------+---------+--------+
 
为什么需要外键约束？
 
-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);
INSERT news_cate(cateName)
VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号'
);
INSERT news(title,content,cateId)
VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

-- 插入一个错误数据
INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);
 
**问题1：**对于上面的这个错误数据，也就是脏数据，在news_cate中并没有对用的新闻种类与它的caseId对应，这显然是不合适的。
 
-- 查询news id,title,content
-- news_cate cateName
SELECT n.id,n.title,n.content,nc.cateName
FROM news AS n
INNER JOIN news_cate AS nc
ON n.cateId=nc.id;
 
+----+-------+---------+----------+
| id | title | content | cateName |
+----+-------+---------+----------+
|  1 | a1    | aaaa1   | 国内新闻 |
|  2 | a2    | aaaa2   | 国内新闻 |
|  3 | a3    | aaaa3   | 体育新闻 |
|  4 | a4    | aaaa4   | 国际新闻 |
|  5 | a5    | aaaa5   | 娱乐新闻 |
+----+-------+---------+----------+
 
**问题2：**假如这个时候我们将news_cate下的国际新闻删除
 
DElETE FROM news_cate WHERE id=2;
 
但是对于news表中的新闻来说，它的caseId还是有2的，那么也就缺少了对应的caseId，这也显然是不合适的。
 
针对上面两个问题，我们就需要使用我们的外键约束。

外键约束
 
外键必须是另一个表的主键。
外键的作用：
1.实体完整性，确保每个实体是唯一的（通过主键来实施）
2.域完整性，确保属性值只从一套可选的集合里选择
3.关联完整性，确保每个外键或null或含有与相关主键值相配的值
foreign key 约束的主要目的是控制存储再外键表中的数据，但它还可以控制对主键表中数据的修改。
 
外键是用来控制数据库中数据的完整性的，就是当你对一个表的数操作时，和它关联的一个或多个表的数据同时发生改变。
 
创建外键约束
 
例题：在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
 
create table audit (
EMP_no int NOT NULL,
create_date datetime NOT NULL,
foreign key(EMP_no)
references employees(id)
);

记录一下碰到的问题。由于我是使用PowerDesigner来建mysql物理模型的，为了表与表之间的关系更加清楚，我给他们连线了。之后我就用它生成的SQL语句在navicat把表建出来，我看见没问题就直接用了，毕竟初学者嘛，嘿嘿。
 到我做到删除的时候，我一般是先在数据库先试试SQL语句可以还是不可以才把它写到项目里面的，然后，在我写好删除的SQL后，运行，它报错了。它不让我删除，说存在外键约束，删除会破坏他们的结构。
 外键约束：让数据库自己通过外键来保证数据的完整性和一致性，让他们的关联性更强。
 虽然它是有用处，但一般是用不上的，而且会让我们写SQL的难度增加。
 有可能带来的问题，当mysql数据库中有两个表，table1和table2，相互关联，在删除表的时候出错：
 
Cannot delete or update a parent row: a foreign key constraint fails
 
很明显这是表关联生成的强制约束问题，在删除的时候回检查表之间的关联关系，从而导致无法删除。
 如果不想删除它的外键约束，其实可以在mysql这样写
 
 这样就可以删除带有外键约束的表的数据了，但是，我还不会写到mybatis的xml里面，而且我感觉它给我的用处不大，所以我决定把我那个数据库里面的外键约束全部删除掉。
 我们先把数据库里面所有的外键约束查出来
 
 
数据库名那里就写你要删除外键约束的数据库名字就行了，然后运行。
 接着他就会查出一堆外键约束给你
 
 
然后把查出来的东西复制到上面执行
 
 
最后就成功了，这下子没有了外键约束的阻碍，我们不是想删除啥就删除啥。

SQL Sever 小技巧之解决外键约束(禁用、启用外键约束)
 
 
 
因为有外键约束的存在,在从表中,修改主表不存在的数据是不合法的;在主表中,删除从表中已存在的主表信息也是不合法的。
 
 
当然方法总比困难多，以下两种方法各有优略，任君选择
 
 
直接删除影响你的外键约束，有时候外键约束的存在是对表之间的数据关联有着重大作用，所以这个方式慎用。
 
 
禁用和启动外键约束(nice)
 
 
禁止或者启用指定表的所有外键约束(nice)
 
 alter table 表名 nocheck constraint all;//禁用
 alter table 表名 check constraint all;//启动
 
禁止或者启用指定表的某个外键约束
 
alter table 表名 nocheck constraint 该表的某个约束  //禁用
alter table 表名 check constraint 该表的某个约束  //启动