数据库主从表的处理 事务VS 数据级联删除

很多时候，我们会碰到这样的场景：“删除一个表的数据的时候，将另一个表的相关数据删除。”

这里是创建两张表的脚本：

CREATE TABLE [dbo].[ProductCategory](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

CREATE TABLE [dbo].[Product](
    [Id] [uniqueidentifier] NOT NULL,
    [CategoryId] [uniqueidentifier] NULL,
    [Name] [varchar](50) NULL,
    [Price] [decimal](18, 0) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

创建后的表大致如下：

 

一些实验数据：

INSERT INTO [Test].[dbo].[ProductCategory] VALUES('4B07A7D0-B56A-4DE3-9F55-972AC6D60994','category1');
INSERT INTO [Test].[dbo].[Product] VALUES(newid(),'4B07A7D0-B56A-4DE3-9F55-972AC6D60994','product1','1');
INSERT INTO [Test].[dbo].[Product] VALUES(newid(),'4B07A7D0-B56A-4DE3-9F55-972AC6D60994','product2','2');
INSERT INTO [Test].[dbo].[Product] VALUES(newid(),'4B07A7D0-B56A-4DE3-9F55-972AC6D60994','product3','3');
INSERT INTO [Test].[dbo].[Product] VALUES(newid(),'4B07A7D0-B56A-4DE3-9F55-972AC6D60994','product4','4');

有很多种方法可以实现这个功能：

 

在模型层中处理：

public class ProductCategoryRepository
{
    public bool DeleteCategory(ProductCategory category)
    { 
        // 删除Category
        // 删除该Category 下面的products.
    }
}

这个比较简单也很容易理解，但是它有个问题：如果是直接通过执行SQL 来删除Category的。那么这个约束就无法满足了，或者是说你必须记得如果要删除Category的话，那么就应该使用DeleteCategory方法。

 

其实可以通过触发器来级联删除：

具体的触发器代码如下：

Create TRIGGER [dbo].[DeleteRelatedProducts] ON  [dbo].[ProductCategory]
 AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    delete from [dbo].[product] where categoryId in 
    (
        select id from deleted
    )
END

这种方式比较简单，而且语法也很明了：

具体资料可以参照：http://msdn.microsoft.com/en-us/library/ms191300(SQL.105).aspx

 

还有一种方式可能并不是很多人知道：

外键的级联删除和更新：

在sql server 中可以通过设置外键的级联删除和更新来实现这个功能。

这里是外键的定义：来自http://baike.baidu.com/view/68073.htm

简介

外键（Foreign Key）

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。换而言之，如果关系模式R中的某属性集不是R的主键，而是另一个关系R1的主键则该属性集是关系模式R的外键，通常在数据库设计中缩写为FK。

外键的作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值

 

我们现在的要求是删除ProductCategory的时候，同时删除该ProductCategory下面的Product。

所以应该在Product 表中建立外键约束，

 

看到删除规则了吗，指定为层叠的话，那么当删除ProductCategory的时候，就会删除Product了。

select * from ProductCategory;
select * from product;

Delete from ProductCategory;

select * from ProductCategory;
select * from product;

结果如下：

上面是表级联删除的帖子，这时候主角应该登场了

其实我们可以使用事务，transaction 来实现这样的行为。

原因：

说这种级联的关系本来就是业务逻辑，需要在程序的业务层来用代码控制，把这种业务关系交给数据库处理是非常不合理的。

我也更倾向于，把代码都写在程序了，尽量少用存储过程。这样也更加有利于跨数据库，和以后的维护。

不过如果逻辑不是很复杂，都是可以考虑使用的。这个就仁者见仁，智者见智了。但事务操作的优势是很明显的。