agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Three way foreign keys
Date: Wed, 28 May 2014 12:24:36 +0100
Message-ID: <201405281224.36915.gary.stainburn@ringways.co.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Message-ID: <201405281224.36915.gary.stainburn@ringways.co.uk>
Permalink: ../201405281224.36915.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201405281224.36915.gary.stainburn@ringways.co.uk
reply
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: gary.stainburn@ringways.co.uk
Subject: Re: Three way foreign keys
In-Reply-To: <201405281224.36915.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox