Thread: Having a 2-column uniqueness constraint

Hello,

I know that it is possible to have a 2-column primary key, which
enforces uniqueness of the combination of those two columns.  However,
for our schema, we need to have a single column (integer) primary key,
but we still wish to enforce uniqueness across two particular columns.

For example:

id (integer primary key) | domain_name (varchar (255)) | mid (other id)

The id is, as shown, the primary key.  The mid is the primary id of
another table (the management unit), with a foreign key constraint that
keeps the reference correct.

How can I make sure that the there is only one instance of a particular
domain name in each management unit?

Put another way, how can I set a (domain_name, mid) UNIQUE constraint?

Thanks,

VanL

On 22 Jul 2003 at 9:29, VanL wrote:
> Put another way, how can I set a (domain_name, mid) UNIQUE constraint?

Set the two fields non-NULL and create a composite unique index on them. That's
what a primary key enforces.

Bye
 Shridhar

--
Newton's Little-Known Seventh Law:    A bird in the hand is safer than one
overhead.

On Tue, Jul 22, 2003 at 09:29:44AM -0600, VanL wrote:
...
> Put another way, how can I set a (domain_name, mid) UNIQUE constraint?

eg:

tree=# create table a ( a1 integer not null, a2 integer not null);
CREATE TABLE
tree=# create unique index a_idx on a(a1,a2);
CREATE INDEX
tree=# \d a
       Table "public.a"
 Column |  Type   | Modifiers
--------+---------+-----------
 a1     | integer | not null
 a2     | integer | not null
Indexes: a_idx unique btree (a1, a2)

tree=# insert into a values (1,2);
INSERT 43341961 1
tree=# insert into a values (1,3);
INSERT 43341962 1
tree=# insert into a values (1,2);
ERROR:  Cannot insert a duplicate key into unique index a_idx

Cheers,

Patrick