When you can't delete due to foreign key constraints, delete in the order "child parent"!

Introduction

I created a batch process in Supabase to delete user information after a certain period of time.

I ran into a problem with the order of deletion processes.

When deleting a table with a foreign key constraint, getting the order wrong results in an error.

This article summarizes a simple solution.

Problem

The Supabase database contains the following two tables:

users (parent table)

user_skill (intermediate table, user_id references users.user_id)

erDiagram
users {
varchar user_id PK
varchar name
}
user_skill {
int8 id PK
varchar user_id FK
int8 skill_id
}

users ||--o{ user_skill : "reference"

When executing the delete process in the order users → user_skill...

Foreign key violation error

If the parent table data is missing, the reference in the intermediate table will be broken.

Solution

The problem was resolved by reversing the deletion order.

Delete user_skill (intermediate table) first

Delete users (parent table) later

Actual code example (TypeScript + Supabase)

// 1. Delete user_skill
const { error: skillError } = await supabase
.from('user_skill')
.delete()
.lt('created_at', `${dateStr}T23:59:59`);

// 2. Delete users
const { error: userError } = await supabase
.from('users')
.delete()
.lt('created_at', `${dateStr}T23:59:59`);

Summary

If there are foreign key constraints, you must always delete in the order "child → parent"

In this case, we'll use user_skill → users. By following this order, we were able to prevent errors.

By keeping database relationships in mind, we can write deletion processes smoothly.