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
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
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'Tweet