Thread: PostgreSQL - Ordering Table based of Foreign Key

PostgreSQL - Ordering Table based of Foreign Key

From
FOUTE K. Jaurès
Date:
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"

Any help is appreciated.
Thank you in advance.

Best regards
Jaurès FOUTE

Re: PostgreSQL - Ordering Table based of Foreign Key

From
Karsten Hilbert
Date:
Am Sun, Oct 03, 2021 at 08:48:13AM +0100 schrieb FOUTE K. Jaurès:

> 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"

drop table if exists ... cascade;

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B



Re: PostgreSQL - Ordering Table based of Foreign Key

From
Thomas Kellerer
Date:
FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
> 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"

You can create the foreign key constraints with the "ON DELETE CASCADE" option.
Then Postgres will handle dependencies automatically for you.

But that means that *all* DELETEs will be cascaded.

Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)

Thomas



Re: PostgreSQL - Ordering Table based of Foreign Key

From
Andreas Joseph Krogh
Date:
På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer <shammat@gmx.net>:
FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
> 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"

You can create the foreign key constraints with the "ON DELETE CASCADE" option.
Then Postgres will handle dependencies automatically for you.

But that means that *all* DELETEs will be cascaded.

Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)
 
There is TRUNCATE ... CASCADE
https://www.postgresql.org/docs/14/sql-truncate.html
 
--
Andreas Joseph Krogh

Re: PostgreSQL - Ordering Table based of Foreign Key

From
FOUTE K. Jaurès
Date:
Thanks for your input but I want to make a delete with where clause 

On Sun, 3 Oct 2021, 10:26 Andreas Joseph Krogh, <andreas@visena.com> wrote:
På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer <shammat@gmx.net>:
FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
> 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"

You can create the foreign key constraints with the "ON DELETE CASCADE" option.
Then Postgres will handle dependencies automatically for you.

But that means that *all* DELETEs will be cascaded.

Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)
 
There is TRUNCATE ... CASCADE
 
--
Andreas Joseph Krogh

Re: PostgreSQL - Ordering Table based of Foreign Key

From
Adrian Klaver
Date:
On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
> Thanks for your input but I want to make a delete with where clause

Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the 
FK would seem to be what you need. If not then you will need to provide 
more information about what you are trying to achieve?


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: PostgreSQL - Ordering Table based of Foreign Key

From
FOUTE K. Jaurès
Date:
I need to delete data from about 100 table in the production system for a specific client without stopping application.

On Sun, 3 Oct 2021, 17:19 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
> Thanks for your input but I want to make a delete with where clause

Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
FK would seem to be what you need. If not then you will need to provide
more information about what you are trying to achieve?


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL - Ordering Table based of Foreign Key

From
Adrian Klaver
Date:
On 10/3/21 9:24 AM, FOUTE K. Jaurès wrote:

Please don't top post, inline/bottom posting is the preferred style on 
this list.

> I need to delete data from about 100 table in the production system for 
> a specific client without stopping application.

Honestly that is not that much more informative.

So:

1) Do the FK's have "ON DELETE CASCADE" already?

2) Does a single FK cascade through all 100 tables or are there groups 
of parent/child tables and associated FK's

3) Show an example DELETE query and tables affected.

> 
> On Sun, 3 Oct 2021, 17:19 Adrian Klaver, <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
>      > Thanks for your input but I want to make a delete with where clause
> 
>     Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
>     FK would seem to be what you need. If not then you will need to provide
>     more information about what you are trying to achieve?
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: PostgreSQL - Ordering Table based of Foreign Key

From
"David G. Johnston"
Date:


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.


Re: PostgreSQL - Ordering Table based of Foreign Key

From
FOUTE K. Jaurès
Date:
Hello,

Thx for all the feedback.
After googling that, I saw this link that can be a way I can go.

Le dim. 3 oct. 2021 à 22:33, David G. Johnston <david.g.johnston@gmail.com> a écrit :


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.




--
Jaurès FOUTE