From ff9cedf9f8093dc87e4974f9ba0c9e591ee6783b Mon Sep 17 00:00:00 2001
From: liyong <18434998025@163.com>
Date: 星期四, 14 五月 2026 14:18:36 +0800
Subject: [PATCH] feat(stock): 添加库存按仓库产品批次唯一约束

---
 doc/20260513_stock_inventory_warehouse_unique.sql |   22 ++++++++++++++++++++++
 1 files changed, 22 insertions(+), 0 deletions(-)

diff --git a/doc/20260513_stock_inventory_warehouse_unique.sql b/doc/20260513_stock_inventory_warehouse_unique.sql
new file mode 100644
index 0000000..104e99c
--- /dev/null
+++ b/doc/20260513_stock_inventory_warehouse_unique.sql
@@ -0,0 +1,22 @@
+-- 搴撳瓨鎸� 浠撳簱ID + 浜у搧瑙勬牸ID + 鎵规鍙� 鍞竴 鐨勮縼绉昏剼鏈�
+-- 鎵ц鍓嶈鍏堝鐞嗛噸澶嶆暟鎹紝骞朵负鍘嗗彶鏁版嵁琛ラ綈 warehouse_info_id銆�
+
+-- 1. 琛ュ瓧娈�
+ALTER TABLE stock_inventory
+    ADD COLUMN IF NOT EXISTS warehouse_info_id BIGINT NULL COMMENT '浠撳簱id';
+
+ALTER TABLE stock_in_record
+    ADD COLUMN IF NOT EXISTS warehouse_info_id BIGINT NULL COMMENT '浠撳簱id';
+
+ALTER TABLE stock_out_record
+    ADD COLUMN IF NOT EXISTS warehouse_info_id BIGINT NULL COMMENT '浠撳簱id';
+
+-- 2. 妫�鏌ラ噸澶嶆暟鎹�
+SELECT warehouse_info_id, product_model_id, batch_no, COUNT(*) AS repeat_count
+FROM stock_inventory
+GROUP BY warehouse_info_id, product_model_id, batch_no
+HAVING COUNT(*) > 1;
+
+-- 3. 鍞竴绱㈠紩
+ALTER TABLE stock_inventory
+    ADD UNIQUE KEY uk_stock_inventory_warehouse_product_batch (warehouse_info_id, product_model_id, batch_no);

--
Gitblit v1.9.3