From: Philip Couling <phil@pedal.me.uk>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Another constant in foreign key problem.
Date: Wed, 22 Feb 2012 12:04:41 +0000
Message-ID: <4F44D9D9.3060201@pedal.me.uk> (raw)
In-Reply-To: <201202221035.11963.gary.stainburn@ringways.co.uk>
References: <201202221035.11963.gary.stainburn@ringways.co.uk>
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
On 22/02/2012 10:35, Gary Stainburn wrote:
> I have three tables,
>
> users - all users of my web site
> facilities - facilities available on my web site
> facility_levels - access levels per user/facility.
>
> One of my facilities is a document library (f_id = 22)
> For this facility I have the levels
>
> select * from facility_levels where fl_f_id=22 order by fl_level;
> fl_f_id | fl_level | fl_desc
> ---------+----------+--------------
> 22 | 1 | Read Only
> 22 | 2 | Add Versions
> 22 | 3 | Amend
> (3 rows)
>
>
> This sets the global access level for the Document Library per user.
>
> I now want to add authentication control on a document or folder level. For
> this I need to create a table library_document_user_level
>
> u_id - user id
> ld_id - library document id
> fl_level - level
>
> The foreign key constraint on fl_level needs to check facility_levels for
> fl_f_id = 22 as well as fl_level existing.
>
> I've googled this but can't find a suitable solution. Can anyone help please.
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: phil@pedal.me.uk, gary.stainburn@ringways.co.uk
Subject: Re: Another constant in foreign key problem.
In-Reply-To: <4F44D9D9.3060201@pedal.me.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