Foreign keys and delete on cascade
As I was working with our PostgreSQL database in Supabase yesterday, I encountered a problem that had me stumped for a bit. Whenever I tried to delete an entry in a
jobs table, I had to first go through the tedious process of deleting related entries in other tables, such as
job_filters. I wanted to find a solution to simplify this process and make deleting a job automatically delete all related job filters or other related table entries.
After some research, I stumbled upon the amazing SQL command
ON DELETE CASCADE. This allows you to add a foreign key relation that will automatically delete related entries in other tables. The only issue was figuring out which tables and foreign key relations to add it to.
Let’s first take a look at my example database structure:
I have 3 tables
job_filters, and a table to connect those two
jobs_job_filters for so called many-to-many relation. The
jobs_job_filters table also have a composite primary key
So now how do I add a foreign key connection between those tables with an automation deletion feature
ON CASCADE DELETE? It’s actually quite simple.
Here is the SQL command I had to execute:
ALTER TABLE jobs_job_filters ADD FOREIGN KEY (job_id) REFERENCES jobs (id) ON DELETE CASCADE, ADD FOREIGN KEY (filter_slug) REFERENCES job_filters (slug) ON DELETE CASCADE;
That’s it, now the tables are connected and the deletion of related filters works!
One thing to be careful about though.
I can run the SQL command as many times as I want, it will always return
success without warming me that the foreign key reference already exists. I had to use this command to check all existing foreign key connections first:
SELECT conname, confrelid::regclass, confkey, confupdtype FROM pg_constraint WHERE confrelid = 'jobs'::regclass AND confrelid IS NOT NULL;
SELECT conname, confrelid::regclass, confkey, confupdtype FROM pg_constraint WHERE confrelid = 'job_filters'::regclass AND confrelid IS NOT NULL;
Also notice, weirdly enough, I had to run these queries on
job_filters tables to see the existing foreign key constrains. If I ran the command on
jobs_job_filter it didn’t show me any foreign key relations, even though I added them on this exact table!
So once I discovered the duplicate foreign keys I could remove them as such:
ALTER TABLE jobs_job_filters DROP CONSTRAINT jobs_job_filters_job_id_fkey; ALTER TABLE jobs_job_filters DROP CONSTRAINT jobs_job_filters_job_id_fkey1; ALTER TABLE jobs_job_filters DROP CONSTRAINT jobs_job_filters_job_id_fkey2; /* And so on */
Hope it helps for someone too!