From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Three way foreign keys
Date: Wed, 28 May 2014 12:24:36 +0100
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: <201405281224.36915.gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hi all,

I'm implementing just-in-time printer consumable ordering within my inventory 
system and utilising SNMP printer interrogation. That bit seems fairly 
straight forward.

The bit I'm stuck with is the schema, which I know I've done before by my 
brain isn't functioning today.

I have the following tables

hw_types		- printer make / model
consumables	- printer consumables
type_consumables	- n-to-n relationship
pieces		- equipment of type in hw_types

I need to create a levels record for each consumables <-> pieces pair.

p_id from pieces
cs_id from consumables

In other words the foreign key constraint needs to look at the type field 
(p_type) in the pieces field and check for the record (p_type / cs_id) 
existing within the type_consumables

Hope that's clear enough. Below are the (simplified) table definitions.

users=# \d hw_types
                                     Table "public.hw_types"
    Column    |         Type          |                         Modifiers                         
--------------+-----------------------+-----------------------------------------------------------
 hwt_id       | integer               | not null default 
nextval('hw_types_hwt_id_seq'::regclass)
Indexes:
    "hw_types_pkey" PRIMARY KEY, btree (hwt_id)
Foreign-key constraints:
    "hw_types_hwt_cat_fkey" FOREIGN KEY (hwt_cat) REFERENCES 
hw_categories(hwc_id)
    "hw_types_hwt_replaced_fkey" FOREIGN KEY (hwt_replaced) REFERENCES 
hw_types(hwt_id)

users=# \d consumables
                                    Table "public.consumables"
   Column    |         Type          |                          Modifiers                          
-------------+-----------------------+-------------------------------------------------------------
 cs_id       | integer               | not null default 
nextval('consumables_cs_id_seq'::regclass)
Indexes:
    "consumables_pkey" PRIMARY KEY, btree (cs_id)
Foreign-key constraints:
    "consumables_cs_type_fkey" FOREIGN KEY (cs_type) REFERENCES 
cons_types(cst_id)

users=# \d type_consumables
Table "public.type_consumables"
 Column |  Type   | Modifiers 
--------+---------+-----------
 hwt_id | integer | not null
 cs_id  | integer | not null
Indexes:
    "type_consumables_unique_index" UNIQUE, btree (hwt_id, cs_id)
Foreign-key constraints:
    "type_consumables_cs_id_fkey" FOREIGN KEY (cs_id) REFERENCES 
consumables(cs_id)
    "type_consumables_hwt_id_fkey" FOREIGN KEY (hwt_id) REFERENCES 
hw_types(hwt_id)

users=# \d pieces
                                          Table "public.pieces"
      Column       |            Type             |                       
Modifiers                       
-------------------+-----------------------------+-------------------------------------------------------
 p_id              | integer                     | not null default 
nextval('pieces_p_id_seq'::regclass)
 p_type            | integer                     | not null
Indexes:
    "pieces_pkey" PRIMARY KEY, btree (p_id)
Foreign-key constraints:
    "pieces_p_type_fkey" FOREIGN KEY (p_type) REFERENCES hw_types(hwt_id)

users=# 
-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql