SQL多对多中间表插入PRIMARY KEY约束冲突问题咨询

直接移除Inventory_Asset中间表的联合主键约束完全不合理，属于典型的为了绕开报错破坏数据一致性的错误设计，会直接导致后续盘点统计逻辑完全失效。

你遇到的主键冲突不是约束设计错误，是业务逻辑和数据写入规则不匹配导致的：

• 你最初设计的「单次盘点关联多个资产、单个资产可出现在多次盘点中」的多对多关系，本身就要求同一个资产在同一次盘点任务里只能对应一条关联记录，(asset_id, inventory_id)的联合主键完全符合数据库三范式要求，是正确的基础设计。
• 测试数据中出现大量重复(asset_id, inventory_id)组合，本质是盘点扫码环节的重复操作（比如同个条码连续扫了多次、接口重复提交），不是表结构的问题。

如果直接删掉联合主键、让中间表变成无主键表，会触发三个很难修复的线上问题：

• 数据冗余失真：同一次盘点里同个资产扫几次就会存几条记录，后续统计资产数量时会出现虚高，比如1台显示器被扫3次就会被统计成3台，直接导致月度盘点报表数据错误，跨期对比资产丢失情况的逻辑也会完全失效。
• 查询性能暴跌：你用的是SQL Server数据库（表名带dbo前缀），无主键堆表在做关联查询、数据删除、增量同步时会频繁触发全表扫描，数据量过百万之后查询延迟会升到秒级甚至更高。
• 脏数据不可逆：没有唯一约束兜底，后续业务迭代中如果出现代码bug写入重复数据，没有简单的方式可以快速去重，只能写脚本全表扫描清理，运维成本极高。

结合你描述的月度条码盘点、多公司资产管理的业务场景，二选一调整即可：

方案1：保留原有联合主键，写入时去重 #

如果业务上不需要记录同一次盘点中同个资产的多次扫码轨迹，只需要保留资产是否被盘点到的状态：

• 保留(asset_id, inventory_id)联合主键不要动
• 写入关联数据时增加重复判断，遇到重复扫码直接跳过插入，不要抛出错误，参考SQL逻辑：

-- 单条写入逻辑
INSERT INTO Inventory_Asset (asset_id, inventory_id, scan_time, scan_operator_id, asset_status)
SELECT @input_asset_id, @current_inventory_id, GETDATE(), @operator_id, @scanned_status
WHERE NOT EXISTS (
    SELECT 1 FROM Inventory_Asset
    WHERE asset_id = @input_asset_id AND inventory_id = @current_inventory_id
)

方案2：扩展主键维度，保留扫码明细 #

如果业务需要留存审计轨迹，要记录同一次盘点中同个资产的多次扫码记录（比如不同管理员扫码、不同时间扫码的记录）：

• 不要删除主键，给中间表新增自增主键列inventory_asset_id作为唯一主键，同时给(asset_id, inventory_id)建立普通非聚集索引满足关联查询性能要求即可。
• 也可以选择把联合主键扩展为(asset_id, inventory_id, scan_time, scan_operator_id)，用更细的操作维度避免重复落库。

针对你提到的多公司租户隔离、多品类资产管理的需求，建议补充几个约束避免后续数据串扰：

• Asset表新增company_id字段，建立(company_id, asset_barcode)唯一索引，保证同一家公司下资产条码唯一，不同公司可以独立维护编码规则互不影响。
• Inventory表新增company_id、inventory_period字段，建立(company_id, inventory_period)唯一约束，保证同一家公司同一个盘点周期（比如2024年1月）只能生成一个盘点截点，避免重复创建盘点任务。
• 中间表Inventory_Asset不需要冗余存储company_id字段，通过关联资产表或者盘点表即可查询到所属公司，避免多表字段不一致导致的数据错误。

内容的提问来源于stack exchange，提问作者Alexandra