1---
2title: Add Constraints Safely in Migrations
3impact: HIGH
4impactDescription: Prevents migration failures and enables idempotent schema changes
5tags: constraints, migrations, schema, alter-table
6---
7 
8## Add Constraints Safely in Migrations
9 
10PostgreSQL does not support `ADD CONSTRAINT IF NOT EXISTS`. Migrations using this syntax will fail.
11 
12**Incorrect (causes syntax error):**
13 
14```sql
15-- ERROR: syntax error at or near "not" (SQLSTATE 42601)
16alter table public.profiles
17add constraint if not exists profiles_birthchart_id_unique unique (birthchart_id);
18```
19 
20**Correct (idempotent constraint creation):**
21 
22```sql
23-- Use DO block to check before adding
24do $$
25begin
26  if not exists (
27    select 1 from pg_constraint
28    where conname = 'profiles_birthchart_id_unique'
29    and conrelid = 'public.profiles'::regclass
30  ) then
31    alter table public.profiles
32    add constraint profiles_birthchart_id_unique unique (birthchart_id);
33  end if;
34end $$;
35```
36 
37For all constraint types:
38 
39```sql
40-- Check constraints
41do $$
42begin
43  if not exists (
44    select 1 from pg_constraint
45    where conname = 'check_age_positive'
46  ) then
47    alter table users add constraint check_age_positive check (age > 0);
48  end if;
49end $$;
50 
51-- Foreign keys
52do $$
53begin
54  if not exists (
55    select 1 from pg_constraint
56    where conname = 'profiles_birthchart_id_fkey'
57  ) then
58    alter table profiles
59    add constraint profiles_birthchart_id_fkey
60    foreign key (birthchart_id) references birthcharts(id);
61  end if;
62end $$;
63```
64 
65Check if constraint exists:
66 
67```sql
68-- Query to check constraint existence
69select conname, contype, pg_get_constraintdef(oid)
70from pg_constraint
71where conrelid = 'public.profiles'::regclass;
72 
73-- contype values:
74-- 'p' = PRIMARY KEY
75-- 'f' = FOREIGN KEY
76-- 'u' = UNIQUE
77-- 'c' = CHECK
78```
79 
80Reference: [Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html)
81