Purely declarative FKs
Hi
Please bear with me, if this is the wrong place or this is an old
question answered over and over again.
Context: In my professional life I rarely come across
projects/applications where there are foreign keys on the database. This
is due to loading freedom not due to that there actually were no
relations. And usually, there is no datamodel. Only the database objects
have been implemented. So, they end up with a bunch/mess of tables, up
to your choice, and relations are insider information.
I have read the PostgreSQL documentation on Foreign keys. It seems, that
it is not possible (easily) to have inactive FKs, so one can have both.
The relation information on the database at least but the freedom of
load order/speed at the same time. My short research on the net spoke
about disabling the trigger used by the FK. If that is possible, this
would be a way but it feels underhanded to me and I would not have a
good feeling to do it.
Question: Are there plans to provide a feature in PostgreSQL that one
can have foreign keys for purely documentation purpose - I know, one
could use a modelling tool and just not implement the FKs, but my
reality is, there is hardly ever a modelling tool involved.
Kind regards
Thiemo
On Oct 16, 2023, at 00:51, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
Question: Are there plans to provide a feature in PostgreSQL that one can have foreign keys for purely documentation purpose - I know, one could use a modelling tool and just not implement the FKs, but my reality is, there is hardly ever a modelling tool involved.
The answer to the specific question is, no, I don't believe that there any plans to implement purely documentary foreign keys.
If you need this information, and need it stored with the schema (as opposed to external documentation), database comments are the most reasonable place to put it.
Christophe Pettus <xof@thebuild.com> writes:
On Oct 16, 2023, at 00:51, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
Question: Are there plans to provide a feature in PostgreSQL that one
can have foreign keys for purely documentation purpose - I know, one
could use a modelling tool and just not implement the FKs, but my
reality is, there is hardly ever a modelling tool involved.
The answer to the specific question is, no, I don't believe that there
any plans to implement purely documentary foreign keys.
Recent versions of the SQL spec have a NOT ENFORCED option for
constraints, which is what I think you're looking for here.
If somebody came with a well-thought-out patch to add that feature,
we'd likely take it. But this is definitely a "scratch your own
itch" situation; I don't know of anyone already working on such
a thing.
regards, tom lane
On 10/16/23 09:06, Tom Lane wrote:
Christophe Pettus <xof@thebuild.com> writes:
On Oct 16, 2023, at 00:51, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
Question: Are there plans to provide a feature in PostgreSQL that one
can have foreign keys for purely documentation purpose - I know, one
could use a modelling tool and just not implement the FKs, but my
reality is, there is hardly ever a modelling tool involved.The answer to the specific question is, no, I don't believe that there
any plans to implement purely documentary foreign keys.Recent versions of the SQL spec have a NOT ENFORCED option for
constraints, which is what I think you're looking for here.
If somebody came with a well-thought-out patch to add that feature,
we'd likely take it. But this is definitely a "scratch your own
itch" situation; I don't know of anyone already working on such
a thing.
NOT ENFORCED would be a great boon to administrators writing regular
archive/purge operations.
--
Born in Arizona, moved to Babylonia.