今日刷题总结10
外键约束
外键 (FK) 是用于在两个表中的数据之间建立和加强链接的一列或多列的组合，可控制在外键表中存储的数据。 在外键引用中，当包含一个表的主键值的一个或多个列被另一个表中的一个或多个列引用时，就在这两个表之间创建了链接。 这个列就成为第二个表的外键。比如表A中的主键是表B中的字段，则该字段就是表B的外键，表A是主表，表B是从表。以MySQL为例，总结一下3种外键约束方式的区别和联系。
假设有学生表student(sid,sname,did)以及院系表depart(did,dname)。sid为student的主键，did为depart的主键且为student的外键。创建depart表的sql语句如下：
create table depart(
did int not null,
dname varchar(50),
primary key(did)
)
然后插入三条记录（1，“计算机学院”），（2，“经贸学院”）。
insert into depart(1,"计算机学院");
insert into depart(2,"经贸学院");
分别以不同的约束方式创建外键引用关系：
（1）NO ACTION：数据库引擎将引发错误，此时将回滚对主表中行的删除或更新操作。
create table student(
sid int not null,
sname varchar(30),
primary key(sid),
foreign key(did) references depart(did) on delete no action on update no action
)
插入3条记录（1，“张三”，1），（2，“李四”，2），（3，“王五”，3）。
insert into student(1,"张三",1);#插入成功
insert into student(2,"李四",2);#插入成功
insert into student(3,"王五",3);#无法插入，院系号3不存在，与参照完整性约束不符
delete from depart where did=1;#从表student中有相关引用，因此主表中无法删除
update depart set did=2 where did=1;#从表中有相关引用，因此主表中无法修改
（2）CASCADE：如果在主表中更新或删除了一行，则将在从表中更新或删除相应的行。
create table student(
sid int not null,
sname varchar(30),
primary key(sid),
foreign key(did) references depart(did) on delete cascade on update cascade
)
插入3条记录（1，“张三”，1），（2，“李四”，2），（3，“王五”，3）。
insert into student(1,"张三",1);#插入成功
insert into student(2,"李四",2);#插入成功
insert into student(3,"王五",3);#无法插入，院系号3不存在，与参照完整性约束不符
delete from depart where did=2;#student中第2条记录级联删除
update depart set did=2 where did=1;#student中第一条记录的did级联修改为2
（3）SET NULL：如果更新或删除了主表中的相应行，则会将构成外键的所有值设置为 NULL。
create table student(
sid int not null,
sname varchar(30),
primary key(sid),
foreign key(did) references depart(did) on delete set null on update set null
)
插入3条记录（1，“张三”，1），（2，“李四”，2），（3，“王五”，3）。
insert into student(1,"张三",1);#插入成功
insert into student(2,"李四",2);#插入成功
insert into student(3,"王五",3);#无法插入，院系号3不存在，与参照完整性约束不符
delete from depart where did=1;#student中第1条记录的did设置为NULL
update depart set did=1 where did=2;#student中第2条记录的did设置为NULL
DELETE语句和TRUNCATE TABLE语句
相同点：
（1）truncate和不带where子句的delete、以及drop都会删除表内的数据。
（2）drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。
不同点：
（1）truncate 和 delete 只删除数据不删除表的结构(定义)。drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
（2）delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
（3）delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动，drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。
（4）速度，一般来说: drop> truncate > delete
（5）安全性：小心使用 drop 和 truncate，尤其没有备份的时候。使用上,想删除部分数据行用 delete，注意带上where子句.，回滚段要足够大。想删除表,当然用 drop想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
（6）delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
（7）TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
（8）TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
（9）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
（10）TRUNCATE TABLE 不能用于参与了索引视图的表。
posted on 2017-08-14 12:29 yifangzhuhou 阅读(179) 评论(0) 编辑 收藏 举报
