Model Compound Alternate Keys

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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?
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

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.
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

roenbaeck
Administrator
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.
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

JanEmilLarsen
You propose:

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

How?
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

roenbaeck
Administrator
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?
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

JanEmilLarsen
Helps a lot - issue completely covered :-)
Reply | Threaded
Open this post in threaded view
|

Re: Model Compound Alternate Keys

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