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