Re: create table with multiple unique fields

Karl,

you can either set the Primary key over all 3 fields to ensure uniquenes
i.e:

create table warehouse (
warehouseno int4,
location varchar(3),
productid varchar(1),
primary key (warehouseno, location, productid)
);

or define a unique index over the 3 fields after the table has been defined:

create table warehouse (
warehouseno int4,
location varchar(3),
productid varchar(1)
);

create unique index idx on warehouse (warehouseno, location, productid);

The difference between the two methods is that NULLS are not allowed in
Primary Keys (method 1) but would be allowed using the unique index (method
2).

Hope it helps

Steve Boyle

"Karl Raven" <lcaasia(at)pd(dot)jaring(dot)my> wrote in message
news:a1agea$qap$1(at)news(dot)tht(dot)net(dot)(dot)(dot)
>
> I'd like to create a table with 3 create unique fields :
>
> fields
> warehouseno,loc, productid, qty, qtyalllocated
>
> i'd like warehouseno,loc, productid to be unique..
> meaning
> 2000, ABC, X - ok
> 2000,ABC,Y -ok
> 2000,ABC,X - reject
> 2000,BCD,X - ok
>
> anyone knows how to do so?
>
> thanks
>
>