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),
primarykey (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@pd.jaring.my> wrote in message
news:a1agea$qap$1@news.tht.net...
>
> 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
>
>