在Sql Server2000的建表过程中碰到的外键、触发器方面的问题

 

收获：1、数据库设计：企业管理器：利器。数据库设计也是很有学问的，此时才知道，数据结构不合理。下面为例：删除冗余的关系，没有级联删除和更新的功能、添加不受限制（原外键），还是可以照常查询的。

2、设计步骤：数据库设计阶段：起码考虑到级联关系，级联添加、删除和更新。全部尝试一遍，免得在编码阶段出现不能级联删除、更新、插入没有外键限制等问题。至少设计的数据库是可以正常使用的。

数据库范式的理解。考虑权限、订单状态等的设计。

在Sql Server2000的建表过程中碰到的外键、触发器方面的问题

在项目进行过程中，碰到了一个比较棘手的问题，惊讶于Sql Server为什么没有这样的功能，简单察看了下其他商业数据库产品，好像也没有这样的功能，于是纳闷，这个有这么难实现吗，还是有其他原因故意不实现？

 

以下是一个简化的模型，其中，Comments中的每一行只能属于People或Book，即BookId与PeopleId只能是其中一个有值，另一个为空。

 

 

问题出在级联删除上，我要的是以下两个逻辑：

1、删除People时，级联删除Book与Comments

2、删除Book时，级联删除Comments

 

粗一看，好像很简单，把那三个外键设为级联删除就OK了，但是，不行，设不进去，原因是“可能会导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。”初一想，好像Sql Server做的也对，它又不知道你的Comments只可能属于People或Book，不可能同时属于People和Book，如果同时属于，另外，这个属于的Book又正好属于那个People时，那么这个Comments不就得被级联删除两次？可是，这个严重吗？似乎不严重，Sql Server应该可以记录一个Comments被级联删除的次数，那么你删除一次不就完了，再不行，你也就甭记录，直接在删除People时删掉级联掉Comments，然后级联删除Book时，就找不到要被再次级联删除的那个Comments了，那不也OK？可是Sql Server没有选择这样做，它选择了阻止这种级联的发生，我不知道为什么。

 

那既然这条路不同，那么我就想着用触发器来做。首先，我设置了People与Book之间为级联删除，为People与Comments、Book与Comments之间设置了“No Action”这样的外键。然后我写下了以下这样的触发器：

 

create trigger PeopleCascadeDelete
on People
after delete
as
begin
delete from Comments where PeopleId in (select id from deleted)
end

 

可是，不行，原因是，该触发器是在People删除之后执行的，而由于外键的关系，在对应的Comments没有删除之前，People自身没法删除，于是这就导致了一个死循环。我们应该先删除掉对应的Comments，然后再删除People。然而，我翻查了联机文档，也google了一把，好像说Sql Server只支持事后触发，不支持事前触发，也就是说，在People被删除之前，没法触发触发器。晕倒，为什么Sql Server不知道事前触发呢？（也是支持，但是我没有找到，因为不存在“before delete”这样的句子，那个“for delete”，好像跟“after delete" 是一样的作用，不知道我没有理解错。）

于是接下来，我想到了使用“Instead of delete”，即用这个触发器完全代替delete的操作，这样真正的delete people语句就不会执行了，但是我可以在触发器中先删除对应的Comments，然后在触发器中补上delete people操作。语句如下：

 

create trigger PeopleCascadeDelete
on People
instead of delete
as
begin
delete from Comments where PeopleId in (select id from deleted)
delete from People where Id in (select id from deleted)
end

 

该语句通过，暗喜了一把，于是如法炮制到Book，问题来了，“无法对表 'Book' 创建 INSTEAD OF DELETE 或 INSTEAD OF UPDATE 触发器 'BookCascadeDelete'。这是因为该表的外键使用级联删除或级联更新。”Sql server非常有道理，你删除People时，是要级联删除Book的，但是你把Delete语句给Instead了，那就不能保证book被级联删除了。

My God。我想我这个表结构不是个设计的很差的表结构吧，问题也不是个大问题吧，但是怎么就这么麻烦呢？？？非得把Comments拆成两张表吗，这样对我的麻烦更大（实际的Comments有很多字段，而且，与Comments关联的表不止两个，而是很多个，以后还可能增加）？

 

我最后采取的是，去掉Comments与People、Book的外键关联，然后再People、Book上使用“after delete”这样的触发器。但感觉总不是那么的让人舒服。