Re: Uniqueness over two fields

DONNA wrote:

> I'm trying to make a database that indexes items sold at online stores.
>
> One table called 'Stock' will contain items sold at every store included
> in the project. It will have the fields ID, SKU, Store.
>
> ID will be a unique primary key that respresents the item
> SKU will be the ID that is used to identify it at that store
> Store will respresent the store that is selling it
>
> Now each store can only sell one of each item so I'd like SKU to be a
> unique field. Problems is, two stores might have the same SKU so
> uniqueness would cause it to break.
>
> What can I do to get around this problem? I could combine SKU and Store
> into one field unique. Or I could make a new table for each Store, but I'd
> rather have everything in one long table as the front-end will be
> displaying all the data together, not per store.

I assume you use some variant of SQL. One can express uniqueness constraints over compound keys directly. Most dialects of SQL force one to combine a uniqueness declaration with an index declaration along the lines of:

create unique index storesku on items(store, sku);

Depending on the index and its expected use, you may get better performance by reversing the columns in the declaration:

create unique index storesku on items(sku, store); Received on Mon Jan 01 2007 - 10:42:05 CST