Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

Only fk constraints may be altered in PostgreSQL

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
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.