From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: Another constant in foreign key problem.
Date: Wed, 22 Feb 2012 12:19:09 +0000
User-Agent: KMail/1.9.10
References: <201202221035.11963.gary.stainburn@ringways.co.uk>
	<4F44D9D9.3060201@pedal.me.uk>
In-Reply-To: <4F44D9D9.3060201@pedal.me.uk>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201202221219.09214.gary.stainburn@ringways.co.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