diff options
Diffstat (limited to 'db')
4 files changed, 48 insertions, 18 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 diff --git a/db/schema/docu-list-rule.ts b/db/schema/docu-list-rule.ts index ec20ab7a..505b1c0d 100644 --- a/db/schema/docu-list-rule.ts +++ b/db/schema/docu-list-rule.ts @@ -72,7 +72,6 @@ export const documentClassOptions = pgTable("document_class_options_new", { // ===== ComboBox Settings 테이블 ===== export const comboBoxSettings = pgTable("combo_box_settings", { id: serial("id").primaryKey(), - projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조 codeGroupId: integer("code_group_id").notNull().references(() => codeGroups.id), // Code Group과 연결 code: varchar("code", { length: 50 }).notNull(), // CODE (예: 100, 201, 202) description: varchar("description", { length: 200 }).notNull(), // Description (예: General, Feed Gas Reveive) @@ -81,9 +80,8 @@ export const comboBoxSettings = pgTable("combo_box_settings", { updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), }, (table) => { return { - // 같은 프로젝트 내에서 codeGroupId와 code 조합은 유니크해야 함 - uniqueProjectCodeGroupCode: unique("unique_project_code_group_code").on( - table.projectId, + // 같은 codeGroupId 내에서 code는 유니크해야 함 + uniqueCodeGroupCode: unique("unique_code_group_code").on( table.codeGroupId, table.code ), @@ -112,7 +110,6 @@ export const documentNumberTypes = pgTable("document_number_types", { // ===== Document Number Type Configs 테이블 ===== export const documentNumberTypeConfigs = pgTable("document_number_type_configs", { id: serial("id").primaryKey(), - projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조 documentNumberTypeId: integer("document_number_type_id").notNull().references(() => documentNumberTypes.id), codeGroupId: integer("code_group_id").references(() => codeGroups.id), // Code Group 참조 sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6) @@ -123,9 +120,8 @@ export const documentNumberTypeConfigs = pgTable("document_number_type_configs", updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), }, (table) => { return { - // 같은 프로젝트 내에서 documentNumberTypeId와 sdq 조합은 유니크해야 함 - uniqueProjectNumberTypeSdq: unique("unique_project_number_type_sdq").on( - table.projectId, + // 같은 documentNumberTypeId 내에서 sdq는 유니크해야 함 + uniqueDocumentNumberTypeSdq: unique("unique_document_number_type_sdq").on( table.documentNumberTypeId, table.sdq ), @@ -138,9 +134,7 @@ export const documentNumberTypeConfigs = pgTable("document_number_type_configs", export const projectsRelations = relations(projects, ({ many }) => ({ codeGroups: many(codeGroups), documentClasses: many(documentClasses), - comboBoxSettings: many(comboBoxSettings), documentNumberTypes: many(documentNumberTypes), - documentNumberTypeConfigs: many(documentNumberTypeConfigs), })) // Code Groups 관계 @@ -177,10 +171,6 @@ export const documentClassOptionsRelations = relations(documentClassOptions, ({ // ComboBox Settings 관계 export const comboBoxSettingsRelations = relations(comboBoxSettings, ({ one }) => ({ - project: one(projects, { - fields: [comboBoxSettings.projectId], - references: [projects.id], - }), codeGroup: one(codeGroups, { fields: [comboBoxSettings.codeGroupId], references: [codeGroups.id], @@ -198,10 +188,6 @@ export const documentNumberTypesRelations = relations(documentNumberTypes, ({ ma // Document Number Type Configs 관계 export const documentNumberTypeConfigsRelations = relations(documentNumberTypeConfigs, ({ one }) => ({ - project: one(projects, { - fields: [documentNumberTypeConfigs.projectId], - references: [projects.id], - }), documentNumberType: one(documentNumberTypes, { fields: [documentNumberTypeConfigs.documentNumberTypeId], references: [documentNumberTypes.id], |