通过数据库设计实现有条件的唯一键约束

问题描述

今天遇到一个有趣的问题，一个业务系统的“客户订单编码”字段存在不唯一问题，导致单据重复。由于客户订单编码来自外部，正常情况下是唯一的，数据库却不能直接添加一个唯一索引来限制。原因是，当单据取消后，终端用户希望重新创建订单时，还可以用相同的客户订单编码。

单据表结构

列名	数据类型	是否主键	备注
DocEntry	int	Y
DocStatus	char(1)	N
NumAtCard	int	N	客户订单编码
DocDate	datetime	N

解决思路

取消单据时，系统会将DocStatus从‘O’改成‘Q’。 如果单纯地将“NumAtCard”和“DocStatus”组合创建唯一约束，并不能解决问题。因为有可能相同的客户订单编码的多个单据都取消的问题。

示例

DocEntry	DocStatus	NumAtCard	DocDate
1	Q	SA88001	2020-04-20
2	Q	SA88001	2020-04-21
3	O	SA88002	2020-04-21

因为是外购既有成熟产品，不能像新开发系统那样灵活（如引入REDIS或者其他分布式唯一控制模块），可以扩展的接口是可以在单据更新时增加触发器，即在数据库层面进行控制。于是，对数据库进行修改，增加一个签名字段用于记录单据取消状态，然后用NumAtCard，DocStatus和签名字段组成唯一索引。

增加字段

列名	数据类型	是否主键	备注
DocEntry	int	Y
DocStatus	char(1)	N
NumAtCard	int	N	客户订单编码
DocDate	datetime	N
CancelSign	nvarchar(50)	N	取消标记签名

在单据更新时的触发器逻辑中增加下面的SQL语句：

/*生成时间戳签名*/
	UPDATE T0 SET T0.CancelSign = CONCAT(DocEntry,'-Q',DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()) - 8 * 3600)
	FROM U_ORDR T0
	WHERE T0.DocEntry = @DocEntry

说明：

1. U_ORDR是上面单据表名；
2. 所用数据库为MS SQL SERVER。

创建唯一索引

新增 “NumAtCard", “DocStatus”, “CancelSign” 组合唯一索引

问题解决。