From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: foreign key question
Date: Wed, 5 Jan 2011 09:53:43 +0000
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201101050953.43830.gary.stainburn@ringways.co.uk>

Hi folks,

I have a table which lists facilities and another table that lists access 
levels for those facilities. All straight forward using a foreign key set up 
using a normal references clause.

users=# select f_id, f_desc from facilities order by f_id;
 f_id |       f_desc                                                                                                                                                                                                                         
------+---------------------                                                                                                                                                                                                                 
    1 | Login                                                                                                                                                                                                                                
    3 | Users                                                                                                                                                                                                                                
.... 
   16 | Itinerary
....
31 rows)

users=# select * from facility_levels ;
 fl_f_id | fl_level |        fl_desc        
---------+----------+-----------------------
      16 |        1 | Own itinerary
      16 |        2 | Dealer/Dept itinerary
      16 |        3 | Dept itinerary
      16 |        4 | Dealer/Dept On/Off
      16 |        5 | Dept On/Off
      16 |        6 | All On/Off
      16 |        7 | All features
(7 rows)

users=# 

Now I want to set up a new access level table specific to the itinerary, along 
the lines of

u_id int4 not null references users(u_id)
fl_level int4 not null references facility_levels(16, fl_level)

Firstly, is this possible, and secondly how would I do it?
-- 
Gary Stainburn
I.T. Manager
Ringways Garages
http://www.ringways.co.uk