Thread: unique index with bool

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

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

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

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;

-----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 <stuart@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

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