agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: J Lumby <johnlumby@hotmail.com>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: foreign key referencing inheritance parent
Date: Fri, 31 Jul 2020 10:07:05 +0300
Message-ID: <445311596177376@mail.yandex.com.tr> (raw)
In-Reply-To: <DM6PR06MB5562927953A8FAA81FE792B1A37B0@DM6PR06MB5562.namprd06.prod.outlook.com>
References: <DM6PR06MB5562927953A8FAA81FE792B1A37B0@DM6PR06MB5562.namprd06.prod.outlook.com>
<div><div>Hi John,</div><div> </div><div>Please find answers below.</div></div><div> </div><div>20.07.2020, 17:44, "J Lumby" <johnlumby@hotmail.com>:</div><blockquote><p>This has come up before elsewhere e.g.<br /><br /><a href="https://stackoverflow.com/questions/26034752/postgresql-inheritance-and-foreign-key-referencing-pare... /><br />but I don't see any mention in any pg mailing list.<br /><br /><br />postgresql permits creation of a foreign key referencing a table which<br />is the parent of child tables via inheritance<br /><br />but enforces that every foreign key value must exist as pkey ONLY in the<br />parent - it throws an ERROR otherwise, even if the pkey exists in a<br />child table.</p></blockquote><div>Having of primary key on child table with the same columns doesn't mean that it guarantees uniqueness of a record among parent and child tables. Each primary key provides uniqueness of records only in the table on which they are created.</div><blockquote><p><br />This is problematic (to me) for three slightly different reasons :<br /><br />1) it is surprising. The expectation is that a reference to<br />accessing row(s) in a parent table will also search all children, as<br />with SELECT, unless the ONLY keyword is specified.<br /><br />which then leads to ...</p></blockquote><div><br />The answer above is also valid here :)</div><blockquote><p><br /><br />2) it is inconsistent with, and less useful than, the somewhat<br />similar CHECK clause in a CREATE/ALTER TABLE :<br /><br />_____________________________________________________<br /><br /> CHECK ( expression ) [ NO INHERIT ]<br /><br />A constraint marked with NO INHERIT will not propagate to child tables.<br /><br />_____________________________________________________<br /><br />For CHECK, the default is propagation to child tables unless<br />explicitly prevented, which is consistent with SELECT and the ONLY<br />qualifier.<br /><br />For FOREIGN KEY there is no optional qualifier to express "propagation" <br />(i.e. propagation of the search for primary key) or "ONLY" and the<br />behaviour is always "ONLY".<br /><br /><br />3) I am probably wrong but as far as I can tell this behaviour is not<br />documented anywhere.</p></blockquote><div><a href="https://www.postgresql.org/docs/12/ddl-inherit.html#DDL-INHERIT-CAVEATS">https://www.postgres... href="https://www.postgresql.org/docs/12/ddl-partitioning.html">https://www.postgresql.org/docs/12/...; - Title 5.11.3.3. Caveats</div><blockquote><p><br /><br />Would there be any interest in providing a choice, e.g. an optional [<br />INHERIT ] on the REFERENCES clause, in a future release?</p></blockquote><div> </div><div>The most important part is why you need to use foreign key referencing to parent table of inheritance. Depending on your answer, there are multiple solution. For example, if the reason why you need it is that you use table partitioning implemented by using triggers and table inheritance, such as in the document below[1], you can switch declarative partitioning with PostgreSQL 12. PostgreSQL 12 supports foreign keys referencing to partitioned tables. [2]</div><div> </div><div>[1]: <a href="https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION-INHERITANCE...;[2]: <a href="https://www.postgresql.org/docs/12/sql-createtable.html">https://www.postgresql.org/docs/12/s... establishing a unique constraint for a multi-level partition hierarchy, all the columns in the partition key of the target partitioned table, as well as those of all its descendant partitioned tables, must be included in the constraint definition.</li><li><code>PRIMARY KEY</code> constraints share the restrictions that <code>UNIQUE</code> constraints have when placed on partitioned tables.</li></ul><div>If table partitioning is not the reason, the most generalized solution can be to use constraint triggers[3] rather than foreign keys. And you can guarantee uniqueness of records among parent and child tables by using check constraints and primary key/unique key constraints together.</div><div> </div><div>[3]: <a href="https://www.postgresql.org/docs/12/sql-createtrigger.html">https://www.postgresql.org/docs/12... />Cheers, John<br /><br /> </p></blockquote><div>Best regards.</div><div>Samed YILDIRIM</div>
view thread (3+ messages) latest in thread
Message-ID: <445311596177376@mail.yandex.com.tr>
Permalink: ../445311596177376@mail.yandex.com.tr/
Also on: postgresql.org/message-id/445311596177376@mail.yandex.com.tr
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: samed@reddoc.net, johnlumby@hotmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: foreign key referencing inheritance parent
In-Reply-To: <445311596177376@mail.yandex.com.tr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox