Marcin Bury wrote:

>
> Let's say I have two tables:
>
> 1. Customers - Customer_ID - Primary Key
> ... other customer fields
>
> 2. Delivery_Locations - Location_ID - Primary Key
> ... other location fields
>
> Since one customer can have many delivery locations and one location can
> serve many customers I know I need third table:
>
> Customers_Locations
> - Customer_ID
> - Location_ID
>
> It's obvious that in this table can be only one pair of certain customer
> and location, so it looks that having compound primary key based on both
> columns is good idea, but...

If you want to enforce the uniqueness of Customer_ID and Location_ID
pairs, you'll want a primary key on the two fields. A second index
on just Location_ID (or whichever field you put second in the primary
key) will give you indexed access for each field, both fields, and
even both fields when neither is an equality lookup.

Cheers,

Ann