22 Feb 2022   sql


Only foreign key constraints may be altered in PostgreSQL:

create extension citext;
create table users (id int generated by default as identity primary key);
create table user_emails (
  user_id int not null references users,
  email citext primary key
);

Now we can see the constraint:

[local] dillon@test=# \d user_emails
             Table "public.user_emails"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 user_id | integer |           | not null |
 email   | citext  |           | not null |
Indexes:
    "user_emails_pkey" PRIMARY KEY, btree (email)
Foreign-key constraints:
    "user_emails_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

But now we can change the foreign key to be deferrable:

alter table user_emails
  alter constraint user_emails_user_id_fkey deferrable initially immediate;

After:

[local] dillon@test=# \d user_emails
             Table "public.user_emails"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 user_id | integer |           | not null |
 email   | citext  |           | not null |
Indexes:
    "user_emails_pkey" PRIMARY KEY, btree (email)
Foreign-key constraints:
    "user_emails_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) DEFERRABLE
🍄