Model Compound Alternate Keys

npeterson

Forgive me if this is really obvious and I haven't quite grasped it yet, but how does one go about making a compound alternate key which should be unique?

JanEmilLarsen

Even more trivial: how to ensure that a single AK-attribute is unique and ensures the "real" entity-integrity that is lost/hidden when using a surrogate key (as represented by an Anchor)?

That is: how to avoid having two instances of the same entity, eg. identified by a (strong) natural key.

Roenbaeck

This is normally done through what we call a "natural key view" on which you impose the constraint. If, for example, a natural key for a Receipt is the combination of the attributes ReceiptNumber and PurchaseDate, then a view is created in which their corresponding tables are joined. Then a uniqueness constraint is created on the view ensuring integrity.

Such views also come in handy when you need to determine whether an instance is known or unknown, or in other words if it has been assigned a surrogate key or not already and what that key is. For this reason the surrogate key (RE_ID) is also often included in the view, but not part of the constraint.

Of course, this depends on the database being able to impose constraints on views, which Microsoft SQL Server can do to some extent and under the right conditions. I have a feeling that 6NF is something most database vendors have given very little thought. Natural keys that are 'spread out' over several anchors may not fulfil those conditions. Say, for example, that the natural key is extended to become ReceiptNumber, PurchaseDate, and StoreNumber, where StoreNumber is an attribute of a Store anchor.

I have started to implement a way to define natural keys in the online modeling tool, but it is not completed yet, and I unfortunately currently have little time for development. For now you will have to create these views and constraints through manual labor. I hope this explains how AK-attributes are supposed to be handled.

Anonymous

You propose:

Then a uniqueness constraint is created on the view ensuring integrity.

How?

Roenbaeck

A very basic minimal working example is this:

create table a (id int not null, value char(1) not null);

create table b (id int not null, value char(1) not null);

create view ab with schemabinding as

select a.id, a.value as aValue, b.value as bValue

from dbo.a join dbo.b on a.id = b.id;

create unique clustered index uq_ab on ab(aValue, bValue);

It can be tested on SQLFiddle:

http://sqlfiddle.com/#!6/80a2e

Of course, in the cases when you are not allowed to create the unique index on the view I suggest just providing the view and ensuring uniqueness at insert time instead. There is a post on how to add uniqueness constraints over temporal data models here:

http://www.anchormodeling.com/?p=1006

I hope that helps?

JanEmilLarsen

Helps a lot - issue completely covered :-)

delostilos

In other database systems (Oracle/PostgreSQL) we can probably use materialized views with unique index on it to achieve the same effect.