Set foreign key to null on delete in #Postgres

Say you have a table with a foreign key:

posts
------
id serial primary key
...
primary_image_id references images (id)

images
------
id serial primary key
post_id references posts (id)

If you attempted to delete an image from the images table while that image's ID is referenced in one of the posts you will receive an error preventing you from deleting the image. Postgres is trying to protect your data integrity by preventing foreign keys that point to records which don't exist.

To prevent errors and allow deleting records from the images table freely you must define the on delete strategy on the posts table.

One of the options available to you is set null so if you are creating a new table it will look like this:

create table posts (
  id int serial primary key,
  -- ...
  primary_image_id int references images (id) on delete set null
);

Now if the primary image is deleted it will set the primary_image_id to null.

This an alternative to on delete cascade which in this case will delete the post from the posts table and is not what we want.

Read the full documentation under '5.3.5. Foreign Keys'