..and on my supplier table a record with PK of 1. So supplier PK 1= has a note of 'some note text'.

How do I se= t up the FK on the notes table? All I can see is the option to link on colu= mn names, so I can set up:

...CONSTRAINT notes_c1 FOREIGN KEY (fo= reignRecNo) REFERENCES supplier ...

BUT that doesn= 't work as far as I can see, as I may have multiple foreignrecnos on notes = with value 1, each of which is dependent on the foreigntablename as well - = but I cannot see how to specify a literal in the FK constraint. What I thin= k I need is something like:

...CONSTRAINT notes_c1 FOREIGN K= EY (foreigntablename,foreignRecNo) REFERENCES supplier ('supplier',recno) .= ..

Does any of that make sens= e?

--=-k5cBHXFPBMFCFTHqAqas-- From lazyvirus@gmx.com Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLaw-0007e3-Tg for pgsql-novice@arkaria.postgresql.org; Tue, 13 Apr 2021 16:08:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lWLav-0004zr-Rj for pgsql-novice@arkaria.postgresql.org; Tue, 13 Apr 2021 16:08:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLav-0004zj-Kd for pgsql-novice@lists.postgresql.org; Tue, 13 Apr 2021 16:08:53 +0000 Received: from mout.gmx.net ([212.227.15.18]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLao-0006PJ-Vr for pgsql-novice@lists.postgresql.org; Tue, 13 Apr 2021 16:08:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1618330124; bh=EQ144RfUPERnCeFJaRLVzfQa8E7fjRz9mVKbTTbU87g=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:In-Reply-To:References; b=W+T1sZYGinw0XINDdWdNIrbpvu+8yZoToGmT/K3mNKG+CqQcXRxDDAx+Sjpi4mwjy zRvRth9RUlaOImoYhadjVyLKStBZ8kj9zrHBpIKhcTnU6DbuGZt9uVZegjnwfKxt2U zjxp4kYLX27oV0atgQpF1EOWyPA0obuC+WjAOcyE= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from mail.defcon1.lan ([93.15.31.113]) by mail.gmx.net (mrgmx004 [212.227.17.184]) with ESMTPSA (Nemesis) id 1N0X8o-1liGLI0LAK-00wSrx; Tue, 13 Apr 2021 18:08:44 +0200 Date: Tue, 13 Apr 2021 18:08:41 +0200 From: Bzzzz To: pgsql-novice@lists.postgresql.org Cc: steve@tusol.co.uk Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 Message-ID: <20210413180841.3ba6e741@msi.defcon1.lan> In-Reply-To: References: <3190280.1618325238@sss.pgh.pa.us> Organization: Anyone, anywhere but in banana demokratik republik of france or UERSS. Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:CuA6eKqZmNZLgF90rkRfiPMh4anXgxyIOxMxrqJkOIanco4h0IC 0ub4g3boxqQXYRrpNELFDr0E4YKrfnL/QcEe6C8NQG3YfoTsucItE8uNubNcpCFTAj/rZRM TdoXUKjN0qMngcc1qUEoNd3sEZjJsCkO7N0LVy4e7mcFom4ARtCelYaXBXox3pJu1qjDutz 3SYL2rh+/sY0QE7Z/+C8w== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:CSEvjrt5k/A=:C60MIdf+5ezGWWVnBEyk46 X7WnfiBZL9/s7xM3RshCPlqQuBFOdR4AZtiBPuA3XPIwdjjcGpPbTGorY9i8oteVRsIBguQi5 ykefXjdgvwpF5VNYkoPMT4PYT+3OyC0d51dienF7dpVP2+5/kQX/xDEI1V5vd9ZyQftiHQu8W LncaTUIzlrVYwOlElO2DvbMcTykCHl2ng7jQuQvpmZn037dLl5RYGCrHIpR9blyDKKEzChiEN mfNc6/vuyUbHSgsrjMtT78qiI3Oe1R+OK0du2ghZtwa82NoORhAssdK6wd5+C1HKC5ddTDy6t JDMmX3GNouKp1Jji3Yi5H7hk2XXAJCFpXOfdzcpvLN8E+245VKZf9zVSZiDeataoWGtU7LCoq PjRKPsyzXzv7fgpDaRYAphX0NnBvuzj08R7wepoeJuzYEYZadtpEdQOllIB3F461R8EiCDZT1 rm3/cUaOTfc+t7OZvO2H6URoOA1DpazwVBPi+4UGP0ThKoIUMy1JhegEN1U03/2TlWWqfrFcV m2d67yZJOCPx1XadVVfnnnkPQcTEae/cnvZP0Hx0Iq/sSOeGfOouVXYa0/f4zKIQObBlcEDBe IebvudQETX0KGhOeYsTjNuuFIzVIXBfo7Hf3jM1MGr5XQ+BKAjgFqw9SytbCvdApnEX7mWp/l Ff705e6kWeaJONZG3YSbaXLuSxi1RX580JZ2hgHlk9w5rgzjBaWIK/sUnLhWWDZuOjSVOp66H 6fLYAbFLWnUCOe+o7Tj+HCftpCzkJUyE8BhLxxPLqEeD0bCsgbZdhvGyVn1lUCdKJogRycL3t BGjzf7OVxWJNFQjTJ/7wQFplS95JTKD2a+hAdyeDLqagyVlQLyx4BuiOxex6M2gnZhj0A2YEb idU2IWRAZ1XgXzGDBhsRnyFOvYaQT8zzV5R8znSdlgsiVzBcKWvc8GSXje+brxW8PLApUOMfV zRH/sQTL1d7WRqQSceZM6tpMZ7oYOjbqMVCYC3zjtpkIjar5UIIkZU63IFUR/4m/b0s5o2xRL qkd0yKJ0TvIF/1zmtkLnOeEnqhuEJ0gZBFtYAsZ2t845ERdzbmJBNrtflfF1j+nvJvDA97caU a/5QLnFyo66OhiLoy92lk8ALafoTHRm2A7xiBasK51p2suojTEDBhUflhAFPa3GNXdfYpw6be QV0nJgNBpGlMhMNATVSvzf7eSu7Huf45dUv8uQYyieZsNkk+2ByFRpHfY696dgrpSvKNg= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 13 Apr 2021 16:48:48 +0100 "Steve Tucknott (TuSol)" wrote: > > Maybe I'm missing something, but aren't you just looking for theON > > DELETE CASCADE option of foreign key constraints? > > regards, tom lane > > Tom, > Maybe it's me that's missing it. > > On my subordinate table I have two 'identification' fields - one > containing a varchar for the table name and the other a value for the > PK for that table. So if I add a 'note' on my notes table for my > suppliers table record 1, I would have an entry on notes with > ...'suppliers', 1, 'some note text'... > . > ..and on my supplier table a record with PK of 1. So supplier PK 1 has > a note of 'some note text'. > > How do I set up the FK on the notes table? All I can see is the option > to link on column names, so I can set up: > ...CONSTRAINT notes_c1 FOREIGN KEY (foreignRecNo) REFERENCES supplier > ... > > BUT that doesn't work as far as I can see, as I may have multiple > foreignrecnos on notes with value 1, each of which is dependent on the > foreigntablename as well - but I cannot see how to specify a literal in > the FK constraint. What I think I need is something like: > ...CONSTRAINT notes_c1 FOREIGN KEY (foreigntablename,foreignRecNo) > REFERENCES supplier ('supplier',recno) ... When you have 2 tables, 'main' & 'dependency', with a referential integrity between them and you want to wipe all rows in dependency when the RI is deleted from main, it should be like that : main ( id int generated always as identity primary key, tm text not null ) dependency ( id int generated always as identity primary key, td text not null, ri_main int not null REFERENCES public.main ON DELETE CASCADE ) This way, when you have a row in 'dependency' that references another one from 'main', if the 'main' row is deleted, the 'dependency' row is automatically also deleted. Read the doc to see what to add if you create both rows in the same transaction. Jean-Yves From lazyvirus@gmx.com Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLdI-0007iz-FM for pgsql-novice@arkaria.postgresql.org; Tue, 13 Apr 2021 16:11:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lWLdH-0000Ey-4B for pgsql-novice@arkaria.postgresql.org; Tue, 13 Apr 2021 16:11:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLdG-0000Er-Uh for pgsql-novice@lists.postgresql.org; Tue, 13 Apr 2021 16:11:18 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWLdF-0005MN-46 for pgsql-novice@lists.postgresql.org; Tue, 13 Apr 2021 16:11:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1618330275; bh=VR/7iI3Mb631/IAGnhvc4T9Z/SIr4u/cPY7OuwazE6I=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:In-Reply-To:References; b=SbB7mGGDtMC+iHtPgKcpvygdByyS0y4HrXGLSPUj3uW9CdcAomvuXzgubdGvxvwK2 pAA9YiY20r1JpLChQ4TLp5ZZK7feciT99Shy79BumcNI1lqKOKhNAC8Z1aFR5HBZW1 iqMPjsMydT3HuXecyKmc+NNTluRD3YGG9tkZDxaY= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from mail.defcon1.lan ([93.15.31.113]) by mail.gmx.net (mrgmx004 [212.227.17.184]) with ESMTPSA (Nemesis) id 1N7i8O-1lb8ld1s0Y-014kc0; Tue, 13 Apr 2021 18:11:15 +0200 Date: Tue, 13 Apr 2021 18:11:13 +0200 From: Bzzzz To: pgsql-novice@lists.postgresql.org Cc: steve@tusol.co.uk Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 Message-ID: <20210413181113.77f7ba46@msi.defcon1.lan> In-Reply-To: References: <3190280.1618325238@sss.pgh.pa.us> Organization: Anyone, anywhere but in banana demokratik republik of france or UERSS. Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:B00j8niR5Ok6ks6oJZjQCa82P9AoAvMFac9JsCqI3AK5BwK/I46 NYtozVPUSQTbDJzgRYTOs+JMGqzVQQQv6YjP1ywYTzKYMw2RLlt7b7WRNbd51fpUF9UgqUr g9l49UNu31qx6MzQKvwGd3ON+sKz22S2J+O9wNhyRhn+2R7GcJKUc67/c3XOBwsSsWjcfF/ moYjq780rfz4wOHtY8BOQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:A6/tSmpdtN0=:QPsnhMiEA6EF5p8LnWM+7k q2z3oKFZHwfdWUX3ivXy+yo65LuubuWW61GfosKc6VvO38r7vPmAismnLfeA9EqS0Ic1oo3ja Mnnh1uzNT9VpMM1DU2J8blm7zH2ViFdh9HgK+v34niKSkDb6IA2Um2VVyXXLfbj/PmvaDLUwr 3PJMccsrjkBap7jTNRM9u+B0fdLZc4erkz9FagQGVu5nAWL0BYoPM2AJU7pzcZ/JgAKJDlGvV vf5oKI8z6SolD4AIrQsFgenWZQFUd6br5OfX/t1X5ZQDUmKJI3mSYDCEGaJIVfWglz+3foN6J hIX0UIU9NDnjn+E/amtoizEwR55wZBNEeMgOPLVRon2sMX7e1v7mB76dFqIsqzi85hQkzU5eD nZpats/p/ani8tnOuE2u/9exyGL8Mw4xFVf3xhwoLVpIpfxSHKgKW4u4v41v2EP6HaFpgAz+Y XdENWtOx7hxSXFI5sI2w13a7W5qdFAiHZAQ1/bFLf1/B1s8vTM655oVMjGStQdrsVZHTqewFg /LMZoCEubrHDOQEsIaqPtCAN85L4oNQFCwEW+XHETlERfunSfjtDGybu8hyCX2SISzI+1VOGS Z6806hbIDarTAOCgeONVKI6NFxlKDMC+JieNWRy8qkJBUhV574ZjJB9ly85zkJ6wbFdb1ViCM QfVwwce/qvK8/5D1BL6+FbgWMDSA3oM4cbSMKl2tJedMmOkMsIARgUIpDE6p0fdIAP+jpibpg KmtL575a4WgJEAy0Oy6LDu8boQ4WrDTvhcwz65QorafbKjGGj9TEhaAc6YJMmk8Ef2M7LKBLg 9dJpkEpCM1KPg2IHCw2tzwJQwuzAyf/w6xyz4oQQaLrYOpgU55s6K/CZjZLObI5BdUDrAyOy9 1M2vOWuqMDvU3I3Q2Xby5OixWYraIUbPtaAEIhEFPgtQLqhUDIxUeG+d19dmDPdzFqNYzgvKv FO6VkgpNPKkmz4larn+c7CnbkyyownELsxJoGgwOFY4LZ+g0Psy9T6V4367yZVHhDeqtdpy2j xHFGna2jfyuRVbcZBnjg+qnh73K2MjUGp/hBL9XCzX8PS/ccN6oBomxHW8UYK8lC8VEmSNGtV LYhUEYnF4+aC0Hdbf6J/pHuMYplOqTbcRxBtg6Z6XW4UWU0O5Fh6QfuhEI9UVhTCfadvJHSmw 36jMb35V4PCrpelXCTwqxOKTDlZPmMkd8rJzxn5FY2gDkb/j1vtgyAEA0ygFDwUkBSIYY= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 13 Apr 2021 16:48:48 +0100 "Steve Tucknott (TuSol)" wrote: > > Maybe I'm missing something, but aren't you just looking for theON > > DELETE CASCADE option of foreign key constraints? > > regards, tom lane > > Tom, > Maybe it's me that's missing it. > > On my subordinate table I have two 'identification' fields - one > containing a varchar for the table name and the other a value for the > PK for that table. So if I add a 'note' on my notes table for my > suppliers table record 1, I would have an entry on notes with > ...'suppliers', 1, 'some note text'... > . > ..and on my supplier table a record with PK of 1. So supplier PK 1 has > a note of 'some note text'. > > How do I set up the FK on the notes table? All I can see is the option > to link on column names, so I can set up: > ...CONSTRAINT notes_c1 FOREIGN KEY (foreignRecNo) REFERENCES supplier > ... > > BUT that doesn't work as far as I can see, as I may have multiple > foreignrecnos on notes with value 1, each of which is dependent on the > foreigntablename as well - but I cannot see how to specify a literal in > the FK constraint. What I think I need is something like: > ...CONSTRAINT notes_c1 FOREIGN KEY (foreigntablename,foreignRecNo) > REFERENCES supplier ('supplier',recno) ... Whoops, it should of course read : REFERENCES public.main(id) ON DELETE CASCADE my bad, sorry. Jean-Yves From steve@tusol.co.uk Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWZMR-0002W4-4i for pgsql-novice@arkaria.postgresql.org; Wed, 14 Apr 2021 06:50:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lWZMP-0002w7-MC for pgsql-novice@arkaria.postgresql.org; Wed, 14 Apr 2021 06:50:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWZMP-0002w0-DX for pgsql-novice@lists.postgresql.org; Wed, 14 Apr 2021 06:50:49 +0000 Received: from smtp.hosts.co.uk ([85.233.160.19]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWZMN-0003rF-P9 for pgsql-novice@lists.postgresql.org; Wed, 14 Apr 2021 06:50:48 +0000 Received: from host86-150-235-122.range86-150.btcentralplus.com ([86.150.235.122] helo=E6540Wired) by smtp.hosts.co.uk with esmtpa (Exim) (envelope-from ) id 1lWZML-0006sT-CQ for pgsql-novice@lists.postgresql.org; Wed, 14 Apr 2021 07:50:46 +0100 Message-ID: Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 From: "Steve Tucknott (TuSol)" Reply-To: steve@tusol.co.uk To: pgsql-novice@lists.postgresql.org In-Reply-To: <20210413180841.3ba6e741@msi.defcon1.lan> References: <3190280.1618325238@sss.pgh.pa.us> <20210413180841.3ba6e741@msi.defcon1.lan> Organization: TuSol Content-Type: multipart/alternative; boundary="=-KnxL1ZSBIgBYw1y+c3W+" Date: Wed, 14 Apr 2021 07:50:45 +0100 MIME-Version: 1.0 User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-KnxL1ZSBIgBYw1y+c3W+ Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit On Tue, 2021-04-13 at 18:08 +0200, Bzzzz wrote: > main ( > id int generated always as identity primary key, > tm text not null > ) > > > dependency ( > id int generated always as identity primary key, > td text not null, > ri_main int not null > REFERENCES public.main ON DELETE CASCADE > ) > > > This way, when you have a row in 'dependency' that references another > one > from 'main', if the 'main' row is deleted, the 'dependency' row is > automatically also deleted. Jean-YvesI think I understand ON DELETE CASCADE and use it on other tables,but I still don't see how that works with the 'main', 'dependency' structure (and my notes). That seems to me to only work if the 'dependency' is only used by 'main', but what if I have other main tables called 'main2', 'main3', 'main4'...that also have records on 'dependency'?My structure handles that as the 'dependency' table also carries the table name of the 'main' - so I could have rows on dependency of:1,'some text for main', 1,'main'2,'some text for main2',1,'main2'3,'some text for main3',1,'main3'.....etcBut the the FK back to main[123] is composed of two parts - the ID and the table name. As a real-life example - I have this data on a working 'notes' and 'document' tables. These are both subordinate tables - subordinate to the tables named in the data:dev_gyb=# select recno,notesforeigntablename,notesforeignrecno from notes order by 3; recno | notesforeigntablename | notesforeignrecno -------+-------- ---------------+------------------- 21 | gybcust | 1 29 | gyblocation | 1 13 | globalnotes | 1 15 | globalnotes | 1 14 | globalnotes | 1 8 | globalnotes | 1 7 | globalnotes | 1 28 | gybgarden | 5 30 | gybplot | 19 22 | calendar | 25 26 | calendar | 28 25 | calendar | 40 ...as you can see, there are records on notes for tables gybcust, gyblocation and globalnotes all with a PK of 1... So '1' cannot be used in isolation.Similarly the 'document' table (carries links to attached documents such as images, pdfs,videos etc) has:dev_gyb=# select recno,docforeigntablename,docforeignrecno from document order by 3; recno | docforeigntablename | docforeignrecno -------+------------ ---------+----------------- 1 | gybgarden | 1 211 | gybcust | 1 26 | gybplant | 1 6 | gyblocation | 1 7 | gybplot | 1 2 | gybgarden | 2 27 | gybplant | 2 8 | gybplot | 2 3 | gybgarden | 3 29 | gybplant | 3 9 | gybplot | 3 10 | gybplot | 4 4 | gybgarden | 4 30 | gybplant | 4....where there are a whole raft of records that point back to owning tables with PKs of 1,2,3,4 - but you can't tell which table without the table name. I feel that I'm being really stupid here and not understanding what I'm being told! Apologies if I haven't grasped what you're telling me. The only answer I can see is to have a column on every table that may carry a note, document, address etc that is its table name. But that seems wrong somehow. Regards,Steve --=-KnxL1ZSBIgBYw1y+c3W+ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

On Tue, 2021-04-13 at 18:08 +0200, Bzzzz wrote:

main (

id &=
nbsp;    int generated always as identity  &n=
bsp;     primary key,

tm&=
nbsp;     text      =
            &nb=
sp;            =
     not null

dependency (


id      int generated always as iden=
tity        primary key,
=

td      text   =
            &nb=
sp;            =
        not null,

ri_main int        &nb=
sp;            =
            &nb=
sp;   not null

   &n=
bsp;    REFERENCES public.main ON DELETE CASCADE<=
pre>
)



This way, when you have a row in 'dependency' that references another one<=
/pre>
from 'main', if the 'main' row is deleted, the 'de=
pendency' row is

automatically also deleted.=

Jean-Yves

I think I understand O= N DELETE CASCADE and use it on other tables,but I still don't see how that = works with the 'main', 'dependency' structure (and my notes). That se= ems to me to only work if the 'dependency' is only used by 'main', bu= t what if I have other main tables called 'main2', 'main3', 'main4'...that = also have records on 'dependency'?

My structure handles that as t= he 'dependency' table also carries the table name of the 'main' - so I coul= d have rows on dependency of:

1,'some text for main', 1,'main'

2,'some text for main2',1,'main2'

3,'some text for main3'= ,1,'main3'

.....etc

But the the FK back to main[123] is= composed of two parts - the ID and the table name.

As a real-life example - I have this data on a working 'notes' and = 'document' tables. These are both subordinate tables - subordinate to the t= ables named in the data:

dev_= gyb=3D# select recno,notesforeigntablename,notesforeignrecno fro= m notes order by 3;

&n= bsp;recno | notesforeigntablename | notesforeignrecno

-------+-----------------------+-----= --------------

&= nbsp; 21 | gybcust &nbs= p; | = 1

= ;29 | gyblocation &nbs= p; | = 1

13 | globalnotes &= nbsp; | &nb= sp; = 1

&n= bsp;15 | globalnotes &= nbsp; | &nb= sp; 1

14 | globalnotes &nbs= p; | = &nb= sp;1

= ; 8 | globalnotes = ; | &= nbsp; 1

7 | globalnotes&nb= sp; | = ; &n= bsp; 1

&nbs= p; 28 | gybgarden = ; | &= nbsp; 5

30 | g= ybplot &nb= sp; | = ; 19

22 | calendar = |&n= bsp; = ; 25

= 26 | calendar &n= bsp; | &nbs= p; 28

25= | calendar &nbs= p; | = 40

...as you can see, there are r= ecords on notes for tables gybcust, gyblocation and globalnotes all with a = PK of 1... So '1' cannot be used in isolation.

Similarly the 'doc= ument' table (carries links to attached documents such as images, pdfs,vide= os etc) has:

dev_gyb=3D# = ; select recno,docforeigntablename,docforeignrecno from document order= by 3;

recno | d= ocforeigntablename | docforeignrecno

-------+---------------------+-----------------=

&nbs= p;1 | gybgarden = | &n= bsp; 1

211 | gybcust = | &n= bsp; 1

26 | gybplant = | &n= bsp; = ; 1

&= nbsp; 6 | gyblocation &= nbsp; | &nb= sp; 1

7 | gybplot &nb= sp; | = ; 1=

&nbs= p;2 | gybgarden = | &n= bsp; 2

27 | gybplant = | &n= bsp; 2

8 | gybplot&n= bsp; = ;| &= nbsp; 2

&nb= sp; 3 | gybgarden = ; | &= nbsp; 3

29 | gybplant = ; | &= nbsp; &nbs= p;3

= 9 | gybplot &nbs= p; | = 3

10 | gybplot = ; | &= nbsp; &nbs= p;4

= 4 | gybgarden &n= bsp; | &nbs= p; 4

30 | gybplant &n= bsp; | &nbs= p; 4

....where there are a whole raft of records that point back to owning= tables with PKs of 1,2,3,4 - but you can't tell which table without the ta= ble name.

I feel that I'm being really stupid here= and not understanding what I'm being told! Apologies if I haven't grasped = what you're telling me.

The only answer I can see = is to have a column on every table that may carry a note, document, address= etc that is its table name. But that seems wrong somehow.

Regards,

Steve

--=-KnxL1ZSBIgBYw1y+c3W+-- From lazyvirus@gmx.com Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWecD-0003Ty-75 for pgsql-novice@arkaria.postgresql.org; Wed, 14 Apr 2021 12:27:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lWecC-0000FV-4P for pgsql-novice@arkaria.postgresql.org; Wed, 14 Apr 2021 12:27:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWecB-0000Cr-St for pgsql-novice@lists.postgresql.org; Wed, 14 Apr 2021 12:27:27 +0000 Received: from mout.gmx.net ([212.227.17.20]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWec7-0006VP-L3 for pgsql-novice@lists.postgresql.org; Wed, 14 Apr 2021 12:27:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1618403242; bh=+ZBkVirMZISKi17ZlwcTPvcTV5Ph5q4htx7MKWcbwdA=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:In-Reply-To:References; b=b1TCkZhZa6Dr5YYjYSXz7gtrcgVw2oZPnZFOkDlQRsywOE/9H9rjN2cV9pPHqedk7 MRwT/9PZygXIAkKfMVL5XvwzmKjYrG5B8iD0QeDi1/vwGXxQM3yLsWE4gI1x2ppdaA ltvLd2La3H/0gjlNawjqT4twI0OElUjIM4RlcSZU= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from mail.defcon1.lan ([93.15.31.113]) by mail.gmx.net (mrgmx104 [212.227.17.174]) with ESMTPSA (Nemesis) id 1MZCfJ-1l24vz0IwT-00V97v; Wed, 14 Apr 2021 14:27:22 +0200 Date: Wed, 14 Apr 2021 14:27:19 +0200 From: Bzzzz To: pgsql-novice@lists.postgresql.org Cc: steve@tusol.co.uk Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 Message-ID: <20210414142719.181ffd62@msi.defcon1.lan> In-Reply-To: References: <3190280.1618325238@sss.pgh.pa.us> <20210413180841.3ba6e741@msi.defcon1.lan> Organization: Anyone, anywhere but in banana demokratik republik of france or UERSS. Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:gcoizL6tUeas8k/MORocovt6ehg8Lye8NRLj5OswkyigDvzGvo5 81QOiStkpsSKVW07FGFrGjQxpxY/eQlDejXF1Dmh5NyShyt3EPYuQIKxVOmJV1pydSA92vL dvh9IAna/HEhHGKMguJnJOGDZukCypmfScMWXcfUkWCUPHESUWZD2ZvlU/wKs6hPH3EprNu 237PtS6JQfEXEg2otFF7A== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:F835B6kHZIk=:Cwg7m3FrYx5pdS60f28Tmm 9Dd5AXcVFurzw/FnqVoCY2BW7m1TxVS4FbeytxjEtVKY9VSIGgqTZILxRLCYJwXe9Zs8L8lq2 bgWa0uVRzoy9txT3M4JEwAEH8SGsWfrXx5HLHJ1+omrcAvDVn8OpVN5ouTmodIb0C4Rkw64fw yfd7d8/VpMyGmVXzu/DQeEXFHajC83MeQK8CiWW1AyM9qFELf3swi86ls+6Dal8/0gY4sgJbh NcR8PSdpYUtHEOwi/fvhqoTAkfmhrqv5zutnZL3l6DgrPVKRGo/KOKti2hxp7SAK4oFWBo9n2 YCkqp40XsrTy7bAWLN8SbnkwoZYgivHU02whiQbH3STBKQ5dsF70r27BxIqgMKfYiJcJ1oDYO kv0mvjvzakDMhW1JCiDMvFyJePHOo5ywinPMeaMlV6MK/6Y+HcPRp2da6py2ycfGL/jHs/e2+ muXR7vSdr9q1kb6uzeuPsPP7sNCjXal+vXpIz4ROCqC2oGh52xFbguvO7XPF5BNowG96t7Jpc nBMAI1K6fuqKw4aXHtfevhcGeO8Sui2P4xi4y/ics85KMYRCDxroxH1lcnU6+TYIWL6F/p502 bQPYnjVA1Prh/1IQ8Z+YuRFyoto3evyNPa/jyvZlH9RPFB5M3hn0hXUqVOtQfZgHhtI+nJN0/ DiNmmGbOMaPjArq4918YQeNyjbK0lhGEx4Qe8SyCVbkVUQ0zqu+VSRMqrQefsHPaeIZvxAg1+ fLOxl5ILT93YKeQfRS235mDZ/ZEGWt7oa08YqkWuZcadR/N8hPqfHKCpT7VaJM8IRqtGEb4uC fhZ1lZfkiu+L+w+KKcS4EbP1nhTsRxmVDRntmn4HmHYIyAqE6dQFR0aIwygfZzKP+0OFHXWYK VI/MWZTYJvUNHHmcAmHCDvWgAEOiY62FF8MZ8uJLbObKw7Oqg1dbV9nwrl+/v+dWNzeLsVArF 3tk9w8rrM0pVU7LN84VG1o5z0jzx6GqnwpLxqFhhjAfbzX/h92dYaK1eC3rDo0RiUKrNG1XkK 4HJiSLvFDsksNwVZNgss9Z+LtlNV6Y/FZ0K8DEelIoRX1PBt7jHrwBhPEcYEvMaRnZE4HmMf6 VB0ag6jT7zMH9zKjiNj/uoYo6D/Z6j55X9H39m3gI/MkhwA/bZUOvL/FWc6KA+jQ9GbQkgptw 0ULBUinOo1SAZ+7CcnaVTrgns11xvih99UhLozsAnv0Ilqb7jXUNhmtxp1JFUVksdoBBg= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 14 Apr 2021 07:50:45 +0100 "Steve Tucknott (TuSol)" wrote: > On Tue, 2021-04-13 at 18:08 +0200, Bzzzz wrote: > > main ( > > id int generated always as identity primary key, > > tm text not null > > ) > >=20 > >=20 > > dependency ( > > id int generated always as identity primary key, > > td text not null, > > ri_main int not null > > REFERENCES public.main ON DELETE CASCADE > > ) > >=20 > >=20 > > This way, when you have a row in 'dependency' that references another > > one > > from 'main', if the 'main' row is deleted, the 'dependency' row is > > automatically also deleted. >=20 > Jean-YvesI think I understand ON DELETE CASCADE and use it on other > tables,but I still don't see how that works with the 'main', > 'dependency' structure (and my notes). That seems to me to only work > if the 'dependency' is only used by 'main', but what if I have other > main tables called 'main2', 'main3', 'main4'...that also have records > on 'dependency'? Oh I missed that, ok, in this case, you need to do 2 things : * add "join" tables with only 2 columns : * a RI to 'main1' (or main2, etc, one table per father) * a RI to 'dependency' with ON DELETE CASCADE | create table schema.main1_dependency( int not null references schema.main1(id) on delete cascade int not null references schema.dependency(id) ) * a trigger for each of these tables that'll delete the concerned 'dependency' rows when a row is deleted from one of these tables (of course, that means you _must_ embed all this into a transaction.) | Note that, if it is what you're looking for, you should be able to=E2=80= =A6 cascade the deletion - ie: you delete a row from 'main5', which, by cascade will delete all pointing rows from a 'main5_dependency' and the trigger will, at last, delete pointing rows from 'dependency'. Looks like that : main1 <--- main1_dependency ---> dependency main2 <--- main2_dependency ---> dependency =E2=80=A6 Creation: [if row doesn't exist into 'main1', transaction may starts here] * create row into 'main1' (ie: 4798) [if row does exist into 'main1', transaction starts here] * create row into 'dependency' (ie: 15025) * create row into 'main1_dependency' to materialize the link (ie: ri_main1=3D2798 & ri_dependency=3D15025) [commit|rollback] Caution: if all of these steps are in only one transaction, you'll have to defer to avoid errors ! Deletion: delete from 'main1' where id=3D4798 CASCADE, deleting from 'main1_dependency' where ri_main1=3D4798 which launch trigger that will delete all rows from 'dependency' when main1_dependency(ri_main1)=3D4798 > My structure handles that as the 'dependency' table > also carries the table name of the 'main' - so I could have rows on > dependency of:1,'some text for main', 1,'main'2,'some text for > main2',1,'main2'3,'some text for main3',1,'main3'.....etcBut the the FK > back to main[123] is composed of two parts - the ID and the table > name.=20 Bad design, because that means you must have a RI column for each and every 'mainN' table into 'dependency' - will works if N < 10, but will start to be a mess when N > 20, unusable if N =E2=89=A5 1000. > I feel that I'm being really stupid here and not understanding what I'm > being told! Apologies if I haven't grasped what you're telling me. No no, my bad, I missed multiple 'mainN' tables. > The only answer I can see is to have a column on every table that may > carry a note, document, address etc that is its table name. But that > seems wrong somehow. > Regards,Steve Jean-Yves From steve@tusol.co.uk Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWw64-0000p7-Bl for pgsql-novice@arkaria.postgresql.org; Thu, 15 Apr 2021 07:07:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lWw63-0003CC-2A for pgsql-novice@arkaria.postgresql.org; Thu, 15 Apr 2021 07:07:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWw62-0003C3-Qu for pgsql-novice@lists.postgresql.org; Thu, 15 Apr 2021 07:07:26 +0000 Received: from smtp.hosts.co.uk ([85.233.160.19]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lWw5y-0000kr-Us for pgsql-novice@lists.postgresql.org; Thu, 15 Apr 2021 07:07:26 +0000 Received: from host86-150-235-122.range86-150.btcentralplus.com ([86.150.235.122] helo=E6540Wired) by smtp.hosts.co.uk with esmtpa (Exim) (envelope-from ) id 1lWw5x-0005MN-9w; Thu, 15 Apr 2021 08:07:21 +0100 Message-ID: Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 From: "Steve Tucknott (TuSol)" Reply-To: steve@tusol.co.uk To: Bzzzz , pgsql-novice@lists.postgresql.org In-Reply-To: <20210414142719.181ffd62@msi.defcon1.lan> References: <3190280.1618325238@sss.pgh.pa.us> <20210413180841.3ba6e741@msi.defcon1.lan> <20210414142719.181ffd62@msi.defcon1.lan> Organization: TuSol Content-Type: multipart/alternative; boundary="=-wyaI21B9SbMt3yHyX7Af" Date: Thu, 15 Apr 2021 08:07:20 +0100 MIME-Version: 1.0 User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-wyaI21B9SbMt3yHyX7Af Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit On Wed, 2021-04-14 at 14:27 +0200, Bzzzz wrote: > Oh I missed that, ok, in this case, you need to do 2 things : > > > > * add "join" tables with only 2 columns : > > * a RI to 'main1' (or main2, etc, one table per father) > > * a RI to 'dependency' > > with ON DELETE CASCADE > > | > > create table schema.main1_dependency( > > int not null references schema.main1(id) on delete cascade > > int not null references schema.dependency(id) > > ) > > > > * a trigger for each of these tables that'll delete the concerned > > 'dependency' rows when a row is deleted from one of these tables > > (of course, that means you _must_ embed all this into a transaction.) > > | > > Note that, if it is what you're looking for, you should be able to… > > cascade the deletion - ie: you delete a row from 'main5', which, by > > cascade will delete all pointing rows from a 'main5_dependency' and the > > trigger will, at last, delete pointing rows from 'dependency'. > > > > Looks like that : main1 <--- main1_dependency ---> dependency > > main2 <--- main2_dependency ---> dependency > > … > > > > Creation: > > [if row doesn't exist into 'main1', transaction may starts here] > > * create row into 'main1' (ie: 4798) > > [if row does exist into 'main1', transaction starts here] > > * create row into 'dependency' (ie: 15025) > > * create row into 'main1_dependency' to materialize the link > > (ie: ri_main1=2798 & ri_dependency=15025) > > [commit|rollback] > > > > Caution: if all of these steps are in only one transaction, you'll have > > to defer to avoid errors ! > > > > Deletion: > > delete from 'main1' where id=4798 > > CASCADE, deleting from 'main1_dependency' where ri_main1=4798 > > which launch trigger that will delete all rows from 'dependency' > > when main1_dependency(ri_main1)=4798 > > > > > My structure handles that as the 'dependency' table > > > also carries the table name of the 'main' - so I could have rows on > > > dependency of:1,'some text for main', 1,'main'2,'some text for > > > main2',1,'main2'3,'some text for main3',1,'main3'.....etcBut the the FK > > > back to main[123] is composed of two parts - the ID and the table > > > name. > > > > Bad design, because that means you must have a RI column for each and > > every 'mainN' table into 'dependency' - will works if N < 10, but > > will start to be a mess when N > 20, unusable if N ≥ 1000. 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? 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. Thanks again for the time and effort you've put into explaining this for me. Regards, Steve --=-wyaI21B9SbMt3yHyX7Af Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

On Wed, 2021-04-14 at 14:27 +0200, Bzzzz wrote:

Oh I missed that, ok, in this case, you need to do 2 thi=
ngs :

* add "join" t=
ables with only 2 columns :

   &nbs=
p;    * a RI to 'main1' (or main2, etc, one table per f=
ather)

       &=
nbsp;* a RI to 'dependency'

  with ON DELETE =
CASCADE

  c=
reate table schema.main1_dependency(

  &=
nbsp;     int not null references schema.main1(id)=
 on delete cascade

     &=
nbsp;  int not null references schema.dependency(id)

* a trigger for each of these tables that'll delete the concerned

  'dependency' rows when a row is deleted from one o=
f these tables

  (of course, that means you _=
must_ embed all this into a transaction.)

|=

  Note that, if it is what you're looking fo=
r, you should be able to=E2=80=A6

  cascade t=
he deletion - ie: you delete a row from 'main5', which, by


  cascade will delete all pointing rows from a 'main5_depende=
ncy' and the

  trigger will, at last, delete =
pointing rows from 'dependency'.


Looks like that :       mai=
n1 <--- main1_dependency ---> dependency

&nb=
sp;            =
           main2 <=
;--- main2_dependency ---> dependency

 &nb=
sp;            =
          =E2=80=A6=



Creation:

<=
/pre>[if row doesn't exist into 'main1', transaction may starts here]<=
/pre>
* create row into 'main1'    &=
nbsp;  (ie:  4798)

[if row does exi=
st into 'main1', transaction starts here]

* create=
 row into 'dependency'  (ie: 15025)

* create =
row into 'main1_dependency' to materialize the link

        (ie: ri_main1=3D2798 &=
; ri_dependency=3D15025)

[commit|rollback]



Caution: if all of these st=
eps are in only one transaction, you'll have

 =
;        to defer to avoid errors !


Deletion:

=
delete from 'main1' where id=3D4798

&nb=
sp;   CASCADE, deleting from 'main1_dependency' where ri_main1=3D=
4798

       &nb=
sp;which launch trigger that will delete all rows from 'dependency'

        when main1_dep=
endency(ri_main1)=3D4798



=
My structure handles that as the 'dependenc=
y' table

also carries the table name of the 'main' - so I could have rows on

<= /blockquote>

dependency of:1,=
'some text for main', 1,'main'2,'some text for


<=
/pre>main2',1,'main2'3,'some text for main3=
',1,'main3'.....etcBut the the FK

back to main[123] is composed of two parts - the ID=
 and the table

name.

Bad design, because that means you must have a RI column for each and<=
/pre>
every 'mainN' table into 'dependency' - will works=
 if N < 10, but

will start to be a mess when N =
> 20, unusable if N =E2=89=A5 1000.

Thanks Jean-Yves, that makes more sense - although I do find 'normalised'=
 structures slightly frustrating even though I can see that the structure w=
orks.

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-depe= ndency' tables?

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.

<= /div>

Thanks again for the time and effort you've put into explaining t= his for me.

Regards,

Steve

<= /html> --=-wyaI21B9SbMt3yHyX7Af-- From lazyvirus@gmx.com Mon Jul 6 19:21:34 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lX1qK-0005Uf-F4 for pgsql-novice@arkaria.postgresql.org; Thu, 15 Apr 2021 13:15:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lX1qI-0003Rd-Mh for pgsql-novice@arkaria.postgresql.org; Thu, 15 Apr 2021 13:15:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lX1qI-0003RV-Ef for pgsql-novice@lists.postgresql.org; Thu, 15 Apr 2021 13:15:34 +0000 Received: from mout.gmx.net ([212.227.15.19]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lX1qD-0001s4-36 for pgsql-novice@lists.postgresql.org; Thu, 15 Apr 2021 13:15:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1618492525; bh=47lezEnN9eD3azqtiB4S7E+O60PlEjEVGV28PJawqno=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:In-Reply-To:References; b=BTEBaGIftXZ47uMmKi2AhBOIDAmVSLpQy5oltfJp3GJWlcmZ3PRgtj3s1eAfmKIr9 34GO6+1M7aPz+VPBcHgg68LMq9kP600lK884Y+qWeY0JA+G9LC/Sn8/Sj4l3oklOut a2uq//LxXocYpuoSUCOam86IHpQjCIYezcmZcFcs= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from mail.defcon1.lan ([93.15.31.113]) by mail.gmx.net (mrgmx004 [212.227.17.184]) with ESMTPSA (Nemesis) id 1MKbgE-1lENxR24Ls-00Ktlh; Thu, 15 Apr 2021 15:15:25 +0200 Date: Thu, 15 Apr 2021 15:15:23 +0200 From: Bzzzz To: pgsql-novice@lists.postgresql.org Cc: steve@tusol.co.uk Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6 Message-ID: <20210415151523.3a59e334@msi.defcon1.lan> In-Reply-To: References: <3190280.1618325238@sss.pgh.pa.us> <20210413180841.3ba6e741@msi.defcon1.lan> <20210414142719.181ffd62@msi.defcon1.lan> Organization: Anyone, anywhere but in banana demokratik republik of france or UERSS. Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:hycB1MWfmP9ds50VOSZ+cJK5ymtcW4yBR59tMPc6wX6EbEE2UcY xDa5wqVZGnu43ViJe3U9YEetmonH9JuPhwy6fxDk/YdxFbdXsjXyc7JMAVYVrq+E6/Sl2Kq 163Ukse9wtfqSbgxUB+ejUdqSaW5w2pZaZULE4cETeJNIZ4Eg92qKaBbgQKvOgqB26OUcSa pJNURmfTAnL17dVY0qQNA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:/llYrKdjduk=:AJi0OQ9C8Z5uv9YmCfniR8 xyKITzgyws3ephejD9mrQ8HfWlPjNNRlVUA36H4r7Tgl6MdxCm1+nJUfAAnTrPo4NQozlzuVV KbhiZQ0zoi8HDyL0hkl0N81ArMZl/+07aLUUazivCLXD6+aPelKtpwQ3wKOoGtb2XrLxuNArN UDLEIpOmDr3NrCFh9agQiJ1ef3K1eROBQWrEWTzA7DtoRAv6bDzxf3hLCUa2duYhT2RD/Ciu9 AOn70d/BrpZ2RuSLO1+ABZXu8qEi9F/gY/v1rmkKthGOwGNbgjEsAxNESKcAtlNU8pkqzIh2I By15imWU0G8chnYlKPKozguaJ9s59HAoVVPQa2KmT7Pyljb9Rr+mVKkn9guRtjM6t+e/nRkvH 0DClC5sx/XmJYhb7kE/vGue5xQt61/mbdRF1PfDQQR3Ucqx3s5mYi7bzcghfeSQUkzjF7b74B NdcsQLvdgKg33YvYuJxBxKmps55Ak6/q+QSrSb11h2TjFJTKlsYY4qPyEC9E+gd9i/dpZnZPM yr4kb4hK6t3eolnRClm19C/Q3wm4vn5SkpMpJP/YxSwW9D6uIbji56Not5xcDwLmGyOdshyER 4PZ5bfiz2T4qIGsrVP1BHMVioGPMurCqlL3mqmSytT5qBoDhH7p3p5AFw2VmDbu/etbOWtsir mSAQugaCgRUisdMA0XZbwIiR6Przwx3UxwwdDjJt5IhZ2xTaOR5JhCf6J5IpKwKqqArCcpIwW 13wOVMVq/khMQOo7jmQrOycfP8dyfqJtramI4e/Ewgiu9y70+/yTEVMjVXuTvhJUZ5bvHCixL n79LUlZgGOPocCpaETLUjwnK7XgS3cyCD7iovdg1kAg+kFjpaci76QZW8Gh9Np3HFQS4RzcTl cAvz0bncqJRhQZ8a6tCkSZe0zjudgNve4zzDIWZWxcjsWZLAN5bIHPo59Ts0Nwo1SZSocrxpx Clv6cY0d9YfgmR1eZoGZhO7j42RIwGQSF/dhzJp3WaWxFDEMT5yH71n+qlDKGIgYgdzymIiLR lmHH6kGCwGkpgeEt8lhdRP3mJ4UMs8HpdYkGBd7lQP1kS1a439MBicIiLR/Qo75iK9U3bNeUB 80CjxEzGSgStp+3rqKYBbm/2S2VJ74sRzOwAUdkdExt4sT0r8S9arr1gDpdHICetaNu3C6Wpm tCslVPOV6SmUnMMB+qY6+9NGQ76OZs3JOgvnX7iS67pHerCp5Y1DMVe6l2BuQGoK7IEfk= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 15 Apr 2021 08:07:20 +0100 "Steve Tucknott (TuSol)" 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, =20 > but why is it any better then having many 'intermediate > main-to- dependency' tables? =20 Because of=E2=80=A6 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. =20 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. =20 You're welcome. Jean-Yves