Re: PostgreSQL - Ordering Table based of Foreign Key - Mailing list pgsql-general

From David G. Johnston
Subject Re: PostgreSQL - Ordering Table based of Foreign Key
Date
Msg-id CAKFQuwaAkpQz0UJzJLBNMWAL9YvXAnVBqJjgsp4G72zA1bKO_w@mail.gmail.com
Whole thread Raw
In response to PostgreSQL - Ordering Table based of Foreign Key  (FOUTE K. Jaurès <jauresfoute@gmail.com>)
Responses Re: PostgreSQL - Ordering Table based of Foreign Key
List pgsql-general


On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès <jauresfoute@gmail.com> wrote:
Hello,

I want to order tables based on the foreign key so that I can delete tables one by one without facing "ERROR: update or delete on table "table" violates foreign key constraint. DETAIL: Key is still referenced from table"

As others have said this is why on delete cascade exists.  Unfortunately this does require some advanced planning as changing it on the fly doesn't really work.

I do not believe there is a built-in way to return an ordered listing of dependent relations given a base relation as an input.  But the catalog entries do exist should you wish to build such yourself.

That said maybe deferred constraint evaluation will work so that at least the order doesn't matter.  But you still.would.need to know which tables to write delete commands for.

There is some recent discussion on making this work in a more user-friendly away but that would be only available in v15 at best.

David J.


pgsql-general by date:

Previous
From: zim
Date:
Subject: Re: Check constraint failure messages
Next
From: "David G. Johnston"
Date:
Subject: Re: Check constraint failure messages