32 lines
1.6 KiB
MySQL
32 lines
1.6 KiB
MySQL
|
-- Migration V5: Fix permit_theme_rules schema and add constraints
|
||
|
|
-- 1. Ensure themes.name is unique
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'themes_name_key') THEN
|
||
|
|
ALTER TABLE public.themes ADD CONSTRAINT themes_name_key UNIQUE (name);
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- 2. Add region_id to permit_theme_rules if missing
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'permit_theme_rules' AND column_name = 'region_id') THEN
|
||
|
|
ALTER TABLE public.permit_theme_rules ADD COLUMN region_id uuid;
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- 3. Add unique constraint to permit_theme_rules
|
||
|
|
-- Note: Since region_id can be NULL, we use a unique index with COALESCE for the constraint effect on NULLs if needed,
|
||
|
|
-- or just a standard unique constraint if we allow different entries for same permit/theme in different regions but only one "global" one.
|
||
|
|
-- Given the Excel structure, it seems we might have one row per permit/theme but different departments per region?
|
||
|
|
-- No, the Excel has "全市各级审批部门" as one column, and then region columns.
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'permit_theme_rules_permit_theme_region_key') THEN
|
||
|
|
-- We'll just do a simple unique constraint for now, but be aware that NULLs in region_id won't collide with each other in standard UNIQUE.
|
||
|
|
-- For our import logic, we'll likely search for (permit_name, theme_id, region_id)
|
||
|
|
ALTER TABLE public.permit_theme_rules ADD CONSTRAINT permit_theme_rules_permit_theme_region_key UNIQUE (permit_name, theme_id, region_id);
|
||
|
|
END IF;
|
||
|
|
END $$;
|