Re: unique index with bool

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

Re: unique index with bool

От:
Scott Marlowe <smarlowe@g2switchworks.com>
Дата:
On Thu, 2005-05-19 at 09:49, tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2);
> 
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
> 
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true'));
> but it's not working.

Not sure if this is what you want:

create unique index on table(param1, param2) where param2 is true;

unique index with bool

От:
tmpmac@mac.com
Дата:
CREATE UNIQUE INDEX name on table(param1,param2);

How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?

I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true'));
but it's not working.

Regards,
Mac

Re: unique index with bool

От:
Richard Huxton <dev@archonet.com>
Дата:
tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2);
> 
> How to create such unique index when param2 is bool type, and this
> param2 should be accepted only in case of true ?
> 
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true'));
>  but it's not working.

Something like:

CREATE UNIQUE INDEX my_uniq_idx ON table(param1,param2)

ALTER TABLE table ADD CONSTRAINT my_constraint CHECK (param2='' OR 
param1=true);

So - separate out the test linking param1/param2 from your uniqueness 
requirement.
-- 
   Richard Huxton
   Archonet Ltd

Re: unique index with bool

От:
Alban Hertroys <alban@magproductions.nl>
Дата:
tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2);
> 
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
> 
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true'));
> but it's not working.

CREATE UNIQUE INDEX name ON table(param1, param2) WHERE param2 = true;

Regards,

-- 
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

Re: unique index with bool

От:
Stuart Bishop <stuart@stuartbishop.net>
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

tmpmac@mac.com wrote:
> CREATE UNIQUE INDEX name on table(param1,param2);
> 
> How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ?
> 
> I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true'));
> but it's not working.

CREATE UNIQUE INDEX foo ON table(param1, (NULLIF(param2, false)))



- --
Stuart Bishop 
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCk/6KAfqZj7rGN0oRAvZ+AJwLWDd8FQe5nBjFDv7ariZ8o8rwLgCfRTdy
BP2yMApbjMDdpDqetUQnX3A=
=k5zA
-----END PGP SIGNATURE-----
FAQ