(박서영)combo box 옵션 및 number type config Add에러 수정

3 files changed, 44 insertions, 0 deletions

diff --git a/db/migrations/0262_remove_project_id_from_combo_box_settings.sql b/db/migrations/0262_remove_project_id_from_combo_box_settings.sql
new file mode 100644
index 00000000..804b471a
--- /dev/null
+++ b/db/migrations/0262_remove_project_id_from_combo_box_settings.sql
@@ -0,0 +1,14 @@
+-- Remove projectId column and related constraints from combo_box_settings table
+-- Since codeGroupId already references codeGroups which has projectId, this is redundant
+
+-- First, drop the unique constraint that includes projectId
+DROP INDEX IF EXISTS "unique_project_code_group_code";
+
+-- Then drop the foreign key constraint for projectId
+ALTER TABLE "combo_box_settings" DROP CONSTRAINT IF EXISTS "combo_box_settings_project_id_projects_id_fk";
+
+-- Finally, drop the projectId column
+ALTER TABLE "combo_box_settings" DROP COLUMN IF EXISTS "project_id";
+
+-- Add a new unique constraint without projectId (since codeGroupId + code should be unique within a code group)
+ALTER TABLE "combo_box_settings" ADD CONSTRAINT "unique_code_group_code" UNIQUE ("code_group_id", "code"); 
\ No newline at end of file
diff --git a/db/migrations/0263_remove_project_id_from_document_number_type_configs.sql b/db/migrations/0263_remove_project_id_from_document_number_type_configs.sql
new file mode 100644
index 00000000..27522555
--- /dev/null
+++ b/db/migrations/0263_remove_project_id_from_document_number_type_configs.sql
@@ -0,0 +1,14 @@
+-- Remove projectId column and related constraints from document_number_type_configs table
+-- Since documentNumberTypeId already references documentNumberTypes which has projectId, this is redundant
+
+-- First, drop the unique constraint that includes projectId
+DROP INDEX IF EXISTS "unique_project_number_type_sdq";
+
+-- Then drop the foreign key constraint for projectId
+ALTER TABLE "document_number_type_configs" DROP CONSTRAINT IF EXISTS "document_number_type_configs_project_id_projects_id_fk";
+
+-- Finally, drop the projectId column
+ALTER TABLE "document_number_type_configs" DROP COLUMN IF EXISTS "project_id";
+
+-- Add a new unique constraint without projectId (since documentNumberTypeId + sdq should be unique within a document number type)
+ALTER TABLE "document_number_type_configs" ADD CONSTRAINT "unique_document_number_type_sdq" UNIQUE ("document_number_type_id", "sdq"); 
\ No newline at end of file
diff --git a/db/migrations/0264_fix_duplicate_document_number_type_configs.sql b/db/migrations/0264_fix_duplicate_document_number_type_configs.sql
new file mode 100644
index 00000000..45269a5e
--- /dev/null
+++ b/db/migrations/0264_fix_duplicate_document_number_type_configs.sql
@@ -0,0 +1,16 @@
+-- Fix duplicate document_number_type_configs data before applying unique constraint
+-- Remove duplicates keeping only the most recent one for each document_number_type_id + sdq combination
+
+-- First, identify and remove duplicates
+DELETE FROM "document_number_type_configs" 
+WHERE id NOT IN (
+  SELECT MAX(id) 
+  FROM "document_number_type_configs" 
+  GROUP BY "document_number_type_id", "sdq"
+);
+
+-- Now we can safely apply the unique constraint
+-- (This should already be applied by the previous migration, but just in case)
+ALTER TABLE "document_number_type_configs" 
+ADD CONSTRAINT "unique_document_number_type_sdq" 
+UNIQUE ("document_number_type_id", "sdq"); 
\ No newline at end of file