Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign key to a partial key

Foreign key to a partial key

From
Simon G
Date:
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
)








Re: Foreign key to a partial key

From
David Johnston
Date:
> I thought of creating a foreign key constraint this way:
> 
> 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.

Add the wbs_name column to the products table.  Since a code is invalid
without knowing which project it belongs to your table definition is wrong.

Then you can create the proper two-column foreign key that references on
primary key on the codes table.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Foreign-key-to-a-partial-key-tp5795291p5795296.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Foreign key to a partial key

From
Simon G
Date:
Thank you very much for your answer, David.

If I add the wbs_name field to the products table, it will only defer the issue to now check if the wbs_name is the wbs_name assigned to the project. The constraint you propose could check if the wbs_name and wbs_code combination exist, but it may allow the case where they belong to another project, when for example, copying a product from another project.

I would like the database model to take into account that, but I do not find a way. As I said before, my option would be for an update or insert trigger to check the projects table, find out the wbs_name and check that the product wbs_code belongs to it.

Or maybe another idea I do not see now.

Regards,
Simon Graffe  


On Sat, Mar 8, 2014 at 10:07 PM, David Johnston <polobo@yahoo.com> wrote:

> I thought of creating a foreign key constraint this way:
>
> 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.

Add the wbs_name column to the products table.  Since a code is invalid
without knowing which project it belongs to your table definition is wrong.

Then you can create the proper two-column foreign key that references on
primary key on the codes table.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Foreign-key-to-a-partial-key-tp5795291p5795296.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


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

Re: Foreign key to a partial key

From
David Johnston
Date:
Simon G wrote
> Thank you very much for your answer, David.
> 
> If I add the wbs_name field to the products table, it will only defer the
> issue to now check if the wbs_name is the wbs_name assigned to the
> project.
> The constraint you propose could check if the wbs_name and wbs_code
> combination exist, but it may allow the case where they belong to another
> project, when for example, copying a product from another project.
> 
> I would like the database model to take into account that, but I do not
> find a way. As I said before, my option would be for an update or insert
> trigger to check the projects table, find out the wbs_name and check that
> the product wbs_code belongs to it.
> 
> Or maybe another idea I do not see now.
> 
> Regards,
> Simon Graffe
> 
> 
> On Sat, Mar 8, 2014 at 10:07 PM, David Johnston <

> polobo@

> > wrote:
> 
>>
>> > I thought of creating a foreign key constraint this way:
>> >
>> > 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.
>>
>> Add the wbs_name column to the products table.  Since a code is invalid
>> without knowing which project it belongs to your table definition is
>> wrong.
>>
>> Then you can create the proper two-column foreign key that references on
>> primary key on the codes table.
>>
>> David J.

And herein lies one of the main issues that arise when using surrogate keys.

I kinda assumed you were running into this problem but the information you
provided was incomplete so it was impossible to be sure.

In this case the probable best answer is to:

1) Add wbs_name to products
2) Add FK pointing to codes using (wbs_name, wbs_code)
3) Add a Unique Index on projects, (wbs_name, project_id)
4) Modify the FK for products->projects to be (wbs_name, project_id)

Since project_id is already unique adding another field to the index cannot
make it any less unique but now the wbs_name is part of a key and so can be
used in the FK relationship.  Since there is only a single wbs_name field on
products that value limits both the codes and projects to be from the same
wbs.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Foreign-key-to-a-partial-key-tp5795291p5795303.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Foreign key to a partial key

From
Simon G
Date:
Hello, David:

You are absolutely right.

I was wrong. Although the wbs_code is not a surrogate key, I was using it like it was.
A product may have a wbs_code, but from a wbs_name, It cannot exists alone. So wbs_name has to be included in the products table. My mistake.

Your solution is what needed to be done. A FK referencing projects (wbs_name, project_id) ensures that the wbs_name used in the product is the proper wbs_name of the project. I already tried it and it works.

Thank you very much.

Simon Graffe