Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Thrasher <thrasher@fibers.upc.es>, pgsql-sql@postgresql.org
Subject: Re: Unique constraint over null values
Date: Mon, 30 Sep 2002 10:33:22 -0700
References: <3D98884F.9070704@fibers.upc.es>
In-Reply-To: <3D98884F.9070704@fibers.upc.es>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209301033.22795.josh@agliodbs.com>


Thrasher,

> where type can be 's' for 'single' and 'x' for extended, so s should=20
> mean that there is only the type and data1 field, and x means that all=20
> fields are set.
>=20
> How can I set a unique constraint like CHECK (type =3D 's' AND UNIQUE=20
> (type, data1)) OR (type =3D 'x' AND UNIQUE (type, data1, data2)) ?
>=20
> The documentation says that 2 NULL values are different, so no unique=20
> constraint can be set over it.

Simple.  Don't use NULLs.   NULL means "unknown".   You should use a=20
differnent value, such as a blank string or "N/A" or "000000000000000" to=
=20
represent "intentionally left blank".

Then you can set data2 NOT NULL and use a regular UNIQUE constraint.

BTW, you have defined your data columns as CHAR, not VARCHAR.  You do know=
=20
that this implies that your data columns will be *exactly* 16 characters in=
=20
length?

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco