agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Foreign key to a partial key
Date: Sun, 9 Mar 2014 09:35:52 -0700 (PDT)
Message-ID: <1394382952219-5795303.post@n5.nabble.com> (raw)
In-Reply-To: <CAH5GJU-AHsVgdfW4G90kw3n-nQghT9Dbb48YNXcT8FC9FGRr7w@mail.gmail.com>
References: <CAH5GJU8_+621Zj4JuC0QOx_h-+ax+pNVM6W5_UFrJJ_nWoBVXw@mail.gmail.com>
<1394332674081-5795296.post@n5.nabble.com>
<CAH5GJU-AHsVgdfW4G90kw3n-nQghT9Dbb48YNXcT8FC9FGRr7w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (5+ messages) latest in thread
Message-ID: <1394382952219-5795303.post@n5.nabble.com>
Permalink: ../1394382952219-5795303.post@n5.nabble.com/
Also on: postgresql.org/message-id/1394382952219-5795303.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Foreign key to a partial key
In-Reply-To: <1394382952219-5795303.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox