Re: Foreign key to a partial key

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.