Re: PostgreSQL - Ordering Table based of Foreign Key

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.