Thread: PostgreSQL - Ordering Table based of Foreign Key
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
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
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
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
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
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
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
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
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.
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