Three way foreign keys
От | Gary Stainburn |
---|---|
Тема | Three way foreign keys |
Дата | |
Msg-id | 201405281224.36915.gary.stainburn@ringways.co.uk обсуждение исходный текст |
Список | pgsql-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 nullcs_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
В списке pgsql-sql по дате отправления: