SQL SERVER添加约束限制序列

针对这样的要求，对一个项目提交采购单，要创建一个采购单的表。这个表至少包含以下字段：项目编号、采购单序号、其他相关内容。我们制定以下规则：针对其中的一个项目编号，插入或者修改的行的采购单序号必须从1,2,3开始的自然数。

也就是说，如下表是合格的：

项目号H213015B的采购单号必须是从1开始，且按顺序增加；不可中间跳过；新插入项目号后，采购单序号又从新从1开始计数。如果在该表中插入(H213015B,6)数据库应该拒绝插入。

从以上可以看出，项目编号和采购单序号一起可以组成该表的键来唯一确定某一行。

可以使用很多方法达到这样的要求：锁，触发器，约束，外部程序控制等等。

我们可以选择约束来完成这样的工作，原因是：锁、触发器会额外增加性能开销；不采用外部程序控制的原因是，我们希望数据库的完整性由数据库本身来控制，而不是借助于外部程序来完成，这样做的好处是保障了多用户之间的操作可能导致规则的打破。

第一步，创建表：

         CREATE TABLE Temp_Sheet
(
Project_Number nchar(10) NOT NULL,
Purchasing_ID int NOT NULL,
PRIMARY KEY(Project_Number,Purchasing_ID)
)

第二部，添加约束所需要的验证函数：

CREATE FUNCTION Check_Number2(@Project_Number_Inserted AS nchar(10),
@Puchasing_ID_Inserted AS int)
RETURNS bit
AS
BEGIN
   DECLARE @result bit=0
   DECLARE @ID int=NULL
   SELECT @ID=Purchasing_ID FROM(SELECT TOP 3 *,ROW_NUMBER() OVER(ORDER BY Purchasing_ID DESC) AS rn FROM dbo.Temp_Sheet WHERE Project_Number=@Project_Number_Inserted AND Purchasing_ID <= @Puchasing_ID_Inserted) tb WHERE rn=2;
   IF (@IDISNOTNULL AND @Puchasing_ID_Inserted=@ID+1)--如果不为空且值增加了1
      SET @result=1
   ELSE
      BEGIN
         SELECT TOP 1 @ID=Purchasing_ID FROM dbo.Temp_Sheet WHERE Project_Number=@Project_Number_Inserted;--如果为空且值为1
         IF(@ID=@Puchasing_ID_Inserted AND @Puchasing_ID_Inserted=1)
            SET @result=1
      END
   RETURN @result
END

第一个SELECT语句针对一个项目号已经有订单了，在该订单上面增加新的订单；或者UPDATE操作所做的约束。

第二个SELECT语句针对一个项目号还没有任何订单所做的约束：要添加订单必须从1开始。

第三步，添加约束：

         ALTER TABLE dbo.Temp_Sheet
ADD CONSTRAINT CK_Puchasing_ID_BY_Project_Number
CHECK (dbo.Check_Number2(Project_Number,Purchasing_ID)=1);

约束所执行的过程：

如果对上述代码能够看懂，我相信你已经知晓在插入已经更新表的时候，一个约束所执行的过程。

那么，根据我个人的理解，一条INSERT，UPDATE，SELECT语句都是作为一个事务来处理，也就是说，要么所有数据插入成功，要么所有数据没有插入成功，不存在只更新或者只插入了一部分数据。

因此该约束所执行的过程为：

1、设置一个起始点A；

2、不管三七二十一，将数据更新或者插入到当前表中；

3、执行键检查，如果有重复的键，则回滚至A，本事务处理结束，如果键检查合格，进入下一步；

4、执行约束处理，如果不满足约束要求，回滚至A；如果满足约束处理，进入下一步；

5、处理触发器等等；

6、完成更新及其插入操作。

根据以上过程，因此我们应该在约束处理函数中这样认为：进入到约束函数执行过程中，可以人为该更新的已经更新了；该插入的已经插入了。因此，在写约束函数时，要认为表已经较UPDATE，INSERT之前的语句不一样。

以上仅为我的理解，如有不同，希望批评指正。