Обсуждение: Thoughts on a cosntraint ?

Поиск
Список
Период
Сортировка

Thoughts on a cosntraint ?

От
stan
Дата:
I have a table that consists of 3 columns.

vendor_key
mfg_key
preferred (boolean)

The idea is that a given vendor is the vendor we want to use for each
manufacturer for a given project.

I need to constrain such that  only on row of mfg, vendor and project can
be set to TRUE.

I would be interested in seeing other peoples approaches on this, also.

Any thoughts? 
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

Re: Thoughts on a cosntraint ?

От
John W Higgins
Дата:
On Sun, Sep 29, 2019 at 12:40 PM stan <stanb@panix.com> wrote:
I have a table that consists of 3 columns.

vendor_key
mfg_key
preferred (boolean)

The idea is that a given vendor is the vendor we want to use for each
manufacturer for a given project.

I need to constrain such that  only on row of mfg, vendor and project can
be set to TRUE.


Partial unique index

Example 3 on this page https://www.postgresql.org/docs/current/indexes-partial.html is exactly what you are looking for.

John W Higgins

Re: Thoughts on a cosntraint ?

От
Rob Sargent
Дата:


On Sep 29, 2019, at 1:52 PM, John W Higgins <wishdev@gmail.com> wrote:

On Sun, Sep 29, 2019 at 12:40 PM stan <stanb@panix.com> wrote:
I have a table that consists of 3 columns.

vendor_key
mfg_key
preferred (boolean)

The idea is that a given vendor is the vendor we want to use for each
manufacturer for a given project.

I need to constrain such that  only on row of mfg, vendor and project can
be set to TRUE.


Partial unique index

Example 3 on this page https://www.postgresql.org/docs/current/indexes-partial.html is exactly what you are looking for.

John W Higgins

Yes but I have to wonder about one-and-only-one preferred vendor: if there are many known, acceptable vendors, wouldn’t a ranking be more useful?  On a crucial commodity it is common practice to continuously use more than one vendor (i.e. multiple preferred vendors).


Re: Thoughts on a cosntraint ?

От
Adrian Klaver
Дата:
On 9/29/19 12:40 PM, stan wrote:
> I have a table that consists of 3 columns.
> 
> vendor_key
> mfg_key
> preferred (boolean)
> 
> The idea is that a given vendor is the vendor we want to use for each
> manufacturer for a given project.
> 
> I need to constrain such that  only on row of mfg, vendor and project can
> be set to TRUE.

Where is the project id?

> 
> I would be interested in seeing other peoples approaches on this, also.
> 
> Any thoughts?
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com