Re: Foreign key to a partial key

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(at)yahoo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>