From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Another constant in foreign key problem.
Date: Wed, 22 Feb 2012 12:19:09 +0000
Message-ID: <201202221219.09214.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <4F44D9D9.3060201@pedal.me.uk>
References: <201202221035.11963.gary.stainburn@ringways.co.uk>
<4F44D9D9.3060201@pedal.me.uk>
Thank you to you both. I was thinking that I wasn't going to be able to do
this.
As the use of these tables is purely to control the web interface to this
database I have decided control this via the application rather than within
the database. It will require less coding and is simpler.
On Wednesday 22 February 2012 12:04:41 Philip Couling wrote:
> Hi Gary
>
> In short you can't have a foreign key here because foreign keys are just
> that: a *key* from another table. fl_level is not a key, it is not
> unique and requires fl_f_id to be unique.
>
> If you want a foreign key between these two tables then you must add the
> facility id to the document library and use a composite key (fl_f_id ,
> fl_level) as the foreign key. This may be advantageous as the same
> structure could be re-used across other facilities (even though it would
> not be applicable to all).
>
>
>
> There are a couple of alternatives to this. One is to add a separate
> column to facility_levels. The sole purpose of this column would be to
> proved a unique key on every row in the table. Foreign keys (such as on
> library_document_user_level) would reference this and not fl_level.
> There would be no constraint here to prevent a row in
> library_document_user_level from referencing the wrong facility.
>
>
> Another possible alternative which is very PostgreSQL specific is to use
> inheritance. Create a child table storing only facility_levels for
> fl_f_id 22. The foreign key would reference the child table and not
> facility_levels. Everything in the child table would also exist in
> facility_levels. As this is so specific to PostgreSQL and is not hugely
> common as a technique, read this as a *possible* solution not a
> recommended one.
>
> Hope this helps
>
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
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: gary.stainburn@ringways.co.uk
Subject: Re: Another constant in foreign key problem.
In-Reply-To: <201202221219.09214.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox