Re: Advice on foreign key and cascading delete design - postgresql 12.6

On Thu, 15 Apr 2021 08:07:20 +0100
"Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)uk> wrote:

[2nd sent, the first one wasn't on the ML]

> Thanks Jean-Yves, that makes more sense - although I do find
> 'normalised' structures slightly frustrating even though I can see that
> the structure works.
> You say that it's bad design having the foreigntablename on the
> dependency as it's not practical if the number of dependent tables is
> large,

> but why is it any better then having many 'intermediate
> main-to- dependency' tables?

Because of… normalized structure ;-p)
Using it, there is no repetition of any kind.

> I think I've gone full circle in that I currently control the orphans
> via hand cranked code - but I hadn't functionalised it. It seems that
> another option is to make the tidy up generic by using the information
> schemas in a function that purges all records that carry a
> 'foreigntablename' and 'foreignrecno' for the main table.

You just read old_table and write new_table with the columns you need
(transforming 'foreigntablename' and 'foreignrecno' references into
foreign keys references in this process), write the wanted functions and
triggers, test them thoroughly in a test DB, triple-check everything's
ok, delete the original table and finally, rename the new_table to the
old name and re-check the whole shebang.

Or you can make a text dump of old_table, modify it with sed and go on
with the rest of the process.

I would do all of this in a test DB then, when everything is checked,
make a dump of it, manually edit this dump to only keep what is needed
and apply it to the regular DB.
This would avoid downtime and make (almost) inevitable blunders
acceptable.

HINT: write down the whole procedure sequentially on a large sheet of
paper with notes where needed, also write down your functions and
triggers, write down the test procedures, triple-check everything's
consistent and proceed step by step with no stress.

> Thanks again for the time and effort you've put into explaining this
> for me.

You're welcome.

Jean-Yves