Thread: Constraint question

Constraint question

From
Andreas Joseph Krogh
Date:
I have the following table:

create table onp_crm_businessfield_company(
businessfield_id integer not null references onp_crm_businessfield(id),
company_id integer not null references onp_crm_relation(id),
is_preferred boolean,
UNIQUE(businessfield_id, company_id)
);


I want a constraint on "is_preffered" so that it's only allowed to be set once
pr. businessfield_id pr. company so that only one businessfield can be
preferred for a company. Does anyone have an idea how to enforce this?

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Skøyen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+


Re: Constraint question

From
Markus Schaber
Date:
Hi, Andreas,

Andreas Joseph Krogh wrote:

> create table onp_crm_businessfield_company(
> businessfield_id integer not null references onp_crm_businessfield(id),
> company_id integer not null references onp_crm_relation(id),
> is_preferred boolean,
> UNIQUE(businessfield_id, company_id)
> );

> I want a constraint on "is_preffered" so that it's only allowed to be set once 
> pr. businessfield_id pr. company so that only one businessfield can be 
> preferred for a company. Does anyone have an idea how to enforce this?

CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id)
WHERE is_prefferred;

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org


Re: Constraint question

From
Andreas Joseph Krogh
Date:
On Thursday 18 May 2006 18:38, Markus Schaber wrote:
> Hi, Andreas,
>
> Andreas Joseph Krogh wrote:
> > create table onp_crm_businessfield_company(
> > businessfield_id integer not null references onp_crm_businessfield(id),
> > company_id integer not null references onp_crm_relation(id),
> > is_preferred boolean,
> > UNIQUE(businessfield_id, company_id)
> > );
> >
> > I want a constraint on "is_preffered" so that it's only allowed to be set
> > once pr. businessfield_id pr. company so that only one businessfield can
> > be preferred for a company. Does anyone have an idea how to enforce this?
>
> CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id)
> WHERE is_prefferred;

Thanks.
I also figured out that an index like this also works:
UNIQUE(company_id, is_preferred)
This works because NULL = NULL is false. But I guess your solution is cleaner.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Skøyen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+


Re: Constraint question

From
Markus Schaber
Date:
Hi, Andreas,

Andreas Joseph Krogh wrote:

>>CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id)
>>WHERE is_prefferred;

> I also figured out that an index like this also works:
> UNIQUE(company_id, is_preferred)
> This works because NULL = NULL is false. But I guess your solution is cleaner.

Yes, it works, but it needs more disk space (it has to index all rows,
and it needs to save both columns), and thus is slower, too.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org