Thread: 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 amodelling tool involved. The answer to the specific question is, no, I don't believe that there any plans to implement purely documentary foreignkeys. If you need this information, and need it stored with the schema (as opposed to external documentation), database commentsare 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.