Обсуждение: Constraint question
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 | | ------------------------+---------------------------------------------+
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
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 | | ------------------------+---------------------------------------------+
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