索引-外键之参照完整性

思考两个问题：
问题一：我们有两个表，一个表(department)存放的是部门的信息，例如部门id，部门名称等；另一个表是员工表(staff)，员工表里面肯定要存放每个员工所在的部门。
那问题来了，如果我们这个时候删除了部门表中的某条记录，在staff表中会发生什么？
问题二：是否有必要给外键加索引。


为了解答上面的问题，让我们先来回顾一下什么是参照完整性。其实，理解了第一个问题即参照完整性问题，第二个问题就迎刃而解了。
我们常常希望保证在一个关系中给定属性集上的取值也在另一个关系的特定属性集的取值中出现。这种情况称为参照完整性(referential integrity)


下面直接给出测试过程：

创建测试表并插入测试数据

-- 部门表
CREATE TABLE DEPARTMENT (
	dept_id	NUMBER(16),
    dept_name VARCHAR2(32),
	primary key(dept_id)
);

-- 员工表
CREATE TABLE STAFF (
	user_id NUMBER(16),
	user_name VARCHAR2(32),
	dept_id	NUMBER(16),
	primary key(user_id),	
	foreign key(dept_id) references DEPARTMENT(dept_id)
	--另一种写法，可以给这个约束命名，而不使用系统默认名字：constraint FK_DEPT_ID foreign key (dept_id) references DEPARTMENT(dept_id)
	--自己命名比较容易管理
);

插入测试数据
insert into DEPARTMENT (DEPT_ID, DEPT_NAME) values (1, '技术部');
insert into DEPARTMENT (DEPT_ID, DEPT_NAME) values (2, '产品部');
insert into DEPARTMENT (DEPT_ID, DEPT_NAME) values (3, '测试部');

insert into STAFF (user_id, user_name, DEPT_ID) values (1, '花花', 1);
insert into STAFF (user_id, user_name, DEPT_ID) values (2, '小明', 1);
insert into STAFF (user_id, user_name, DEPT_ID) values (3, '小常', 2);
insert into STAFF (user_id, user_name, DEPT_ID) values (4, '张宝', 2);
insert into STAFF (user_id, user_name, DEPT_ID) values (5, '赵胜', 2);
insert into STAFF (user_id, user_name, DEPT_ID) values (6, '王五', 2);
insert into STAFF (user_id, user_name, DEPT_ID) values (7, '星儿', 3);

进行参照完整性测试：

1、在从表中插入一条主表不存在的外键数据

insert into STAFF (user_id, user_name, DEPT_ID) values (8, 'xxxxx', 4);

--Error : ORA-02291: integrity constraint (WLMEDICAL.SYS_C0039797) violated - parent key not found
--翻译：违反完整性约束(WLMEDICAL.SYS_C0039797) - 

2、删除主表中一条记录，且该键值在从表外键中存在

delete from DEPARTMENT where dept_id=1;
--Error : ORA-02292: integrity constraint (WLMEDICAL.SYS_C0039797) violated - child record found
--翻译：违反完整性约束(WLMEDICAL.SYS_C0039797) - 有子记录存在

3、更新主表记录，且该键值在从表外键中存在

update DEPARTMENT set dept_id=4 where dept_id=1;
--Error : ORA-02292: integrity constraint (WLMEDICAL.SYS_C0039797) violated - child record found
--翻译：违反完整性约束(WLMEDICAL.SYS_C0039797) - 有子记录存在

由上操作可以看出，设置了外键以后，对主表进行操作都会去从表检查是否会违反参照完整性约束，所以我们可以给外键添加索引以加快检查速度；
同时，进行连接查询的时候速度也会加快。故：给外键加索引还是很有必要的。

参照完整性扩展：

当我们违反了参照完整性约束时，通常的处理(系统默认)是拒绝执行导致完整性破坏的操作（即进行更新操作的事务被回滚）。
当然，在foreign key子句中可以指明：如果被参照关系上的删除或更新动作违反了约束，
那么系统必须采取一些步骤通过修改参照关系中的元祖来恢复完整性约束，而不是拒绝这样的操作。

接着刚才的测试数据继续，

-- 首先删除外键
ALTER TABLE STAFF DROP constraint FK_DEPT_ID SYS_C0039797;
-- 重新声明外键，添加 级联删除、级联更新属性

这是数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。update 则是主键表中被参考字段的值更新，delete是指在主键表中删除一条记录：
on update 和 on delete 后面可以跟的词语有四个
no action ， set null ， set default ，cascade
no action 表示 不做任何操作，
set null 表示在外键表中将相应字段设置为null
set default 表示设置为默认值
cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除

-- mysql 可以:
alter table STAFF add constraint FK_DEPT_ID foreign key (DEPT_ID) references DEPARTMENT4(DEPT_ID) on delete cascade on update cascade;

-- oracle:
ALTER TABLE STAFF 
ADD constraint FK_DEPT_ID
foreign key (dept_id)
references DEPARTMENT(dept_id)
on delete cascade ;

-- 查看当前表的索引(oracle)
select * from user_indexes where table_name='STAFF';

-- 查看当前表的索引(mysql)
show index from STAFF;

注： oracle中不支持级联更新，因为oracle认为，作为外键的一般都是主表的主键，通常，主键是常量是不应该有update的，任何的主键update都是bad practice。
如果出现 更新primary key的情况，那么说明设计有问题，Primary Key肯定不是真正应该设定的Primary Key而是Surrogate Key，完全就是设计失误。bad design 。
所以oracle这样设计是有其道理的。
oracle 中可以使用触发器 或者写存储过程，实现级联更新。

-- 设置了级联删除以后，再删除主表中的数据，则从表的对应的记录也会被删除
delete from DEPARTMENT where DEPT_ID=3;
select * from DEPARTMENT;	-- 剩余 dept_id 1 2
select * from staff;		-- dept_id为3的记录被删掉了。

总结：
1、由于有了外键声名相关联的on delete cascade子句，如果删除department中的元祖导致了此参照完整性约束被违反，
则删除并不被系统拒绝，而是对staff关系作联机删除，即删除参照了被删除系的元祖。类似的，on update cascade会在更新时同步进行参照关系中元祖的更新。
SQL还允许foreign key子句指明除了cascade以外的其他动作，如果约束被违反，可将参考与置为null(用set null代替 cascade)，或者置为默认值(set default)。
但是，一般来说，我们习惯的用法是，不允许删除。如果实在要删除，我觉得应该开发者自己写代码进行备份然后删除，这样也方便日后查询、追踪、留痕等相关操作。

2、回归主题，索引，从以上可以看出，设置外键以后 主表、从表之间是有级联关系的，操作(更新、删除)主表中的数据的时候系统会自动扫描从表中的数据，如果外键有了索引肯定扫描会加快很多，
还有我们平时查询从表记录的时候也会连接主表，
所以，综上，我们应该给从表的外键设置索引，以加快对主表的更新、删除操作，加快从表连接查询速度。