问题描述
今天遇到一个有趣的问题,一个业务系统的“客户订单编码”字段存在不唯一问题,导致单据重复。由于客户订单编码来自外部,正常情况下是唯一的,数据库却不能直接添加一个唯一索引来限制。原因是,当单据取消后,终端用户希望重新创建订单时,还可以用相同的客户订单编码。
单据表结构
列名 | 数据类型 | 是否主键 | 备注 |
---|---|---|---|
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
说明:
- U_ORDR是上面单据表名;
- 所用数据库为MS SQL SERVER。
创建唯一索引
新增 “NumAtCard", “DocStatus”, “CancelSign” 组合唯一索引
问题解决。