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.

A new TABLE for each store is silly; it should be one TABLE with stoire as part of the PK.

If you are tracking a store's items, and have many stores in the TABLE,  the natural PK of the TABLE is the store and the item id. The sku should not be in the PK, so enforcing uniqueness is for data integrity and throwing a unique INDEX (or CONSTRAINT) on store/sku makes a lot of sense.

B. Received on Tue Jan 02 2007 - 18:04:14 CET