Re: Another constant in foreign key problem.

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