Re: Using FKs to non PK unique-indexes

before.the.gods_at_gmail.com wrote:
>
> 1- Normalization
>
> Regarding the normalization question, it is clear at first glance that
> the attribute ORDERS_PRODUCTS.clientID is related only to part of the
> primary key - the column ORDERS_PRODUCTS.orderID - which is a clear
> violation of 2NF. But my understanding of how 2NF violations should be
> resolved is to put any redundancy in a separate table.
>
> As you said, it is basically the case here. ORDERS_PRODUCTS.clientID
> is redundant, but it is fully constrained by ORDERS.clientID, which
> eliminate any eventual update problems.
>
> That's a little bit why I asked my question ... is this model still
> considered not-normalized with ORDERS_PRODUCTS.clientID being redundant
> and constrained at the same time?

Yes, it is still considered not-normalized. Although the redundancy is guarded by constraints, you still have the problem that some updates to, for example, clientID need to be done on several tupels to satisfy the constraints, whereas if you were more normalized this might only be one tuple.

> 2- Candidate key vs PK superset
>
> I understand candidate keys as a set of column(s), completely different
> from the PK column(s) set, having a unicity without violating 2NF or
> 3NF.

A candidate key is a minimal set of columns having unicity. No more, no less. Whether it violates a normal form or not is irrelevant. So the PK is itself also a candidate key. Also note that there can be overlap between the PK and a candidate key.

> In our case, the unique-index created on clientID and orderID is only a
> superset of the PK. It is not a candidate key since any group of
> columns embedding the PK is trivially unique.

Indeed.

> So the question is still opened .... does MSSQL violates database
> relational theory by allowing multi-unicities on an entity?

The answer is still no. :-) At best it is being a bit silly beause it doesn't realize that the unicity constraint is redundant. Also note that strictly speaking the unicity constraint is not the same as declaring that it is a candidate key because there is no claim that it is minimal, i.e., there may be a proper subset that also has the unicity property.

• Jan Hidders