处理SQL 2008/08 中的OUTPUT与CHECK约束冲突的问题

         在SQL Server 2005中，新增的OUTPUT子句极大地提高了数据转移的方便性，不过很遗憾的是， OUTPUT子句的输出表不能包含启用状态的CHECK约束。这未免让人觉得很不爽。不过，在实际使用过程中，却发现了一个很奇怪的问题，某些具有启用状态的CHECK约束的表确实无法用于OUTPUT子句，而某些表却是可以的，经过对比，发现了这个很奇怪现象的原因，使得可以把有启用状态的CHECK约束的表用于OUTPUT子句。

         下面是演示。

USE tempdb;

GO

 

-- 建立测试表

CREATE TABLE dbo.tb_source(

    id int

);

 

CREATE TABLE dbo.tb_target(

    id int,

    CONSTRAINT CHK__tb_target__id

       CHECK(

           id > 0)

);

GO

 

-- OUTPUT 测试1

DELETE dbo.tb_source

    OUTPUT deleted.*

       INTO dbo.tb_target;

 

/*-- 会收到错误

消息333，级别16，状态1，第3 行

OUTPUT INTO 子句的目标表'dbo.tb_target' 不能具有任何启用的检查约束或任何启用的规则。找到检查约束或规则'CHK__tb_target__id'。

--*/

GO

 

-- 禁用和启用约束

ALTER TABLE dbo.tb_target

    NOCHECK CONSTRAINT ALL;

 

ALTER TABLE dbo.tb_target

    CHECK CONSTRAINT ALL;

GO

-- OUTPUT 测试2

DELETE dbo.tb_source

    OUTPUT deleted.*

       INTO dbo.tb_target;

      

/*-- 测试成功

 

(0 行受影响)

--*/

GO

 

-- 验证CHECK 约束是否正常工作

INSERT dbo.tb_target

VALUES(

    -1);

/*-- 结果(违反约束)

消息547，级别16，状态0，第3 行

INSERT 语句与CHECK 约束"CHK__tb_target__id"冲突。该冲突发生于数据库"tempdb"，表"dbo.tb_target", column 'id'。

语句已终止。

--*/

GO

 

 

-- 验证OUTPUT 时, CHECK 约束是否正常工作

INSERT dbo.tb_source

VALUES(

    -1);

 

DELETE dbo.tb_source

    OUTPUT deleted.*

       INTO dbo.tb_target;

 

/*-- 结果(违反约束)

 

(1 行受影响)

消息547，级别16，状态0，第6 行

DELETE 语句与CHECK 约束"CHK__tb_target__id"冲突。该冲突发生于数据库"tempdb"，表"dbo.tb_target", column 'id'。

语句已终止。

--*/

GO

 

 

-- 删除测试

DROP TABLE dbo.tb_source, dbo.tb_target;

 

         从测试可以看出，只要CHECK约束是使用WITH NOCHECK启用的，则可以作为OUTPUT子句的输出表（与微软给的错误提示信息显示不一样）。

         而且这种情况，同样适用于使用WITH NOCHECK创建的约束，参考下面的脚本。

USE tempdb;

GO

 

-- 建立测试表

CREATE TABLE dbo.tb_source(

    id int

);

 

CREATE TABLE dbo.tb_target(

    id int

);

ALTER TABLE dbo.tb_target

    WITH NOCHECK

    ADD CONSTRAINT  CHK__tb_target__id

           CHECK(

              id > 0);

GO

 

-- OUTPUT 测试

DELETE dbo.tb_source

    OUTPUT deleted.*

       INTO dbo.tb_target;

 

/*-- 测试成功

 

(0 行受影响)

--*/

GO

 

-- 删除测试

DROP TABLE dbo.tb_source, dbo.tb_target;

 

注：
         1. 默认情况下，建立约束使用的是WITH CHECK，而启用约束使用的是WITH NOCHECK；
         2. WITH NOCHECK只是确定在建立（启用）约束时，是否检查表中的现有数据，对新进的数据没有影响；
         3. WITH NOCHECK会导致分区视图失败。