Foreign key to a partial key - Mailing list pgsql-sql
Hello:
Will you please hep me with this problem?
I have projects.
Each project is assigned a WBS.
Each WBS is described in a codes table, see structure below.
Each project has many products and each product can belong to a WBS node, see structure below.
I want to enforce that if a product is assigned a wbs_code, that code exists.
I thought of creating a foreign key constraint this way:
products table:
CONSTRAINT wbs_code FOREIGN KEY (wbs_code)
REFERENCES codes (wbs_code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
But it does not make reference to the wbs_name so a product could be assigned to a WBS_code that belongs to another project and the constraint would not be violated. For example, if a user is copying products from another project.
The constraint would need to make reference to the wbs_name assigned to the project, but at the table products, no column contains that info.
My guess is that it can only be constrained in an insert or update trigger that checks that the wbs_node belongs to the wbs_name assigned to the project.
A suggestion, somebody?
Thanking you in advance,
Simon Graffe
Table structures:
CREATE TABLE codes
(
wbs_name character varying(10) NOT NULL,
wbs_code character varying(20) NOT NULL,
description character varying(60),
CONSTRAINT wbs_name_code PRIMARY KEY (wbs_name , code)
)
CREATE TABLE products
(
id integer NOT NULL DEFAULT nextval('idproduct_seq'::regclass),
codprod character varying(35) NOT NULL,
idproject integer NOT NULL,
...
wbs_code character varying(20),
CONSTRAINT idproduct PRIMARY KEY (id),
CONSTRAINT idproject FOREIGN KEY (idproject)
REFERENCES projects (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)