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 jobs, 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 job_id, filter_slug.

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;

and

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 jobs and 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!