Re: Using FKs to non PK unique-indexes

From: Brian Selzer <>
Date: Sat, 11 Feb 2006 02:47:12 -0500
Message-ID: <egMFg9tLGHA.740_at_TK2MSFTNGP12.phx.gbl>

The following schema eliminates the redundant clientID from ORDERS_PRODUCTS while still enabling the database engine to enforce the constraints declaratively:

clientID PK

productID PK

clientproductID PK
clientID AK and FK to CLIENTS.clientID
productID AK and FK to PRODUCTS.productID

orderID PK
clientID FK to CLIENTS.clientID

orderproductID PK
orderID AK and FK to ORDERS.orderID
clientproductID AK and FK to CLIENTS_PRODUCTS.clientproductID

Note: the alternate keys in CLIENTS_PRODUCTS and ORDERS_PRODUCTS are composite keys and should be enforced with a unique constraint.

<> wrote in message
> Hello
> Recently, I decided to use the MSSQL feature that allows an FK to point
> to a unique-index that is *not* a PK. I must say that this lead me to
> several - not to say existential - questions about database modeling
> and theory. I am basically looking for your opinions on the subject.
> Before discussing the case, here is a simplification of my design
> (tables in capitals) :
> productID PK
> clientID PK
> clientID PK and FK on CLIENTS.clientID
> productID PK and FK on PRODUCTS.productID
> orderID PK
> clientID FK on CLIENTS.clientID
> Everything's normal until here. The CLIENTS_PRODUCTS table (in case
> you ask) holds prices and similar stuff because the company (a) does
> not sell all products to the same price and (b) does not sell all
> products to all clients.
> The only table we still need to introduce is the one that will hold the
> order's products :
> orderID PK
> productID PK
> OK for the PK ... but how do I set the FKs on that entity? The main
> integrity problem here is to make sure that the products included in an
> order belong to the client that placed the order.
> So lets do it that way :
> orderID PK
> productID PK | FK on CLIENTS_PRODUCTS.productID
> clientID | CLIENTS_PRODUCTS.clientID
> Perfect ... but how do I set the FK between ORDERS and ORDERS_PRODUCTS?
> One might say I have 2 options :
> 1- I put the FK between ORDERS_PRODUCTS.orderID and ORDERS.orderID.
> 2. I extend the ORDERS PK table to the clientID column and I put the FK
> Option 1 is absolutely out of question since it would create a very
> weak integrity model (allowing an order to have products that are not
> part of the order's client profile).
> Option 2 has more sens ... but I dont like it for 2 reasons. First,
> having the ORDERS table PK on both clientID and orderID has no logic.
> It gives the impression (and actually allows the situation) that a
> single orderID can be repeated for many clients. Second, it creates a
> situation where all subsequent relationship entities that "inherit"
> from ORDERS will be stuck with the clientID column in their PK.
> I then came up with a third option :
> 3- I put a unique-index on ORDERS.orderID and ORDERS.clientID and point
> the FK on that index
> Everything is perfect, no? Well ... this practice is new for me ...
> and I am asking myself several questions :
> 1- if both PKs and a unique-indexes can be pointed by FKs, what is the
> difference between a PK and a UI? (beside the little yellow key sign
> that appears beside PK columns in diagrams)
> 2- Having unique-indexes on an entity gives the impression that there
> is more than one PK on an entity ... is this conform to database
> theory?
> 3- Is my model normalized?
> Thank for your comments.
Received on Sat Feb 11 2006 - 08:47:12 CET

Original text of this message