UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Composite Indexes vs Composite Primary Keys    
Composite Indexes vs Composite Primary Keys

Keys are sets of attributes. A composite key is a key consisting of more than one attribute - multiple attributes making up a single key.

In the relational model of data composite keys are fundamentally important. Relational databases would not be possible without composite keys. Before E.F.Codd’s relational model (1969), databases relied on “navigational” storage models that sorted and linked data using addresses or pointers between data elements. With data models of that kind, a single attribute (very probably an artificial one created specifically for the purpose) is sufficient for identifying and navigating between data elements.

The crucial innovation that Codd made was the idea of an abstraction of all data as relations whereby data was stored, accessed and manipulated by its natural structure alone. The attributes of data themselves are what identify information in a relational database. Relations consist of unique tuples (“rows”) and the tuples represent unique instances of things in the real world (more precisely, tuples are unique propositions; statements of fact about the world outside the database).

Real things don’t always come with single-valued identifiers attached to them. To identify correctly the things we wish to model in a relational database and to ensure that facts are uniquely and accurately recorded it is absolutely necessary to allow their identifying keys to be sets of attributes rather than just single values.

For more discussion of these concepts:

Natural Vs Surrogate Keys

Normalization Terms and Concepts (Glossary of Terms)

An index is an internal storage structure very often used in support of keys. Jet/ACE, in common with many other DBMSs, requires an index to support the enforcement of uniqueness constraints on key columns. Such "Unique Indexes" are not to be confused with the keys they enforce. A unique index is simply a physical mechanism for enforcing uniqueness. A key is the (minimal, non-nullable) set of attributes against which uniqueness is being enforced.

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 12,846 times.  This page was last modified 20:56, 23 January 2014 by ButtonMoon. Contributions by multicoat, Brent Spaulding, Jack Leach and Cpetermann  Disclaimers