(김준회) AVL 피드백 반영 (이진용 프로 건)

2 files changed, 33 insertions, 10 deletions

diff --git a/db/schema/NONSAP/nonsap.ts b/db/schema/NONSAP/nonsap.ts
index 322e9647..838ebf29 100644
--- a/db/schema/NONSAP/nonsap.ts
+++ b/db/schema/NONSAP/nonsap.ts
@@ -54,21 +54,21 @@ export const cmctbCd = nonsapSchema.table('cmctb_cd', {
   // 사용자정의문자열255
   USR_DF_CHAR_20: text(),
   // 사용자정의체크1
-  USR_DF_CHK_1: varchar({ length: 1 }),
+  USR_DF_CHK_1: varchar({ length: 10 }),
   // 사용자정의체크2
-  USR_DF_CHK_2: varchar({ length: 1 }),
+  USR_DF_CHK_2: varchar({ length: 10 }),
   // 사용자정의체크3
-  USR_DF_CHK_3: varchar({ length: 1 }),
+  USR_DF_CHK_3: varchar({ length: 10 }),
   // 사용자정의체크4
-  USR_DF_CHK_4: varchar({ length: 1 }),
+  USR_DF_CHK_4: varchar({ length: 10 }),
   // 사용자정의체크5
-  USR_DF_CHK_5: varchar({ length: 1 }),
+  USR_DF_CHK_5: varchar({ length: 10 }),
   // 사용자정의체크6
-  USR_DF_CHK_6: varchar({ length: 1 }),
+  USR_DF_CHK_6: varchar({ length: 10 }),
   // 사용자정의체크7
-  USR_DF_CHK_7: varchar({ length: 1 }),
+  USR_DF_CHK_7: varchar({ length: 10 }),
   // 사용자정의체크8
-  USR_DF_CHK_8: varchar({ length: 1 }),
+  USR_DF_CHK_8: varchar({ length: 10 }),
   // 사용자정의일자1
   USR_DF_DT_1: varchar({ length: 8 }),
   // 사용자정의일자2
diff --git a/db/schema/avl/avl.ts b/db/schema/avl/avl.ts
index 0b983168..30c82ced 100644
--- a/db/schema/avl/avl.ts
+++ b/db/schema/avl/avl.ts
@@ -1,4 +1,4 @@
-import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView } from "drizzle-orm/pg-core";
+import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView, uniqueIndex } from "drizzle-orm/pg-core";
 import { createInsertSchema, createSelectSchema } from "drizzle-zod";
 import { sql } from "drizzle-orm";
 
@@ -118,7 +118,30 @@ export const avlVendorInfo = pgTable("avl_vendor_info", {
   // 타임스탬프
   createdAt: timestamp("created_at").defaultNow(),
   updatedAt: timestamp("updated_at").defaultNow(),
-});
+}, (table) => ({
+  // 표준 AVL용 unique 제약조건 (isTemplate = true)
+  // 표준AVL용 필드들 + 자재그룹코드 + 협력업체명이 unique해야 함
+  uniqueStandardAvl: uniqueIndex("unique_standard_avl_material_vendor")
+    .on(
+      table.constructionSector,
+      table.shipType,
+      table.avlKind,
+      table.htDivision,
+      table.materialGroupCode,
+      table.vendorName
+    )
+    .where(sql`${table.isTemplate} = true`),
+  
+  // 프로젝트 AVL용 unique 제약조건 (isTemplate = false)
+  // 같은 projectCode 내에서 자재그룹코드 + 협력업체명이 unique해야 함
+  uniqueProjectAvl: uniqueIndex("unique_project_avl_material_vendor")
+    .on(
+      table.projectCode,
+      table.materialGroupCode,
+      table.vendorName
+    )
+    .where(sql`${table.isTemplate} = false`),
+}));
 
 // Zod 스키마 생성
 export const insertAvlListSchema = createInsertSchema(avlList);