Unique key

A key is used in a relational database to the tuples ( records) to identify a relation (table) clear them numbers. A key is then a set of columns, which is selected so that each row in the table with the values ​​of this column group has a unique combination of values ​​.

• 6.1 Definition
• 6.2 foreign key and relationship types

Introduction

In the theory of relational databases are per entity, one or more candidate keys is required, which must be unique by definition. Of these key candidates a candidate is selected as the primary key and is implemented in the implementation of the entity as a database table as such. Notwithstanding this convention there are database systems that allow the definition of tables without a primary key is defined. Such tables so allow duplicate records and are therefore by definition not relational entities.

In relational databases, a distinction the key concepts

• Super key: set of attributes (fields) in a relation ( table), which identify the tuples (rows) in this relation clearly, so in pairs selected tuples contain ( one also says, " are clearly " ) always have different values. A trivial super key would be, for example, the set of all attributes of a relation in common ( trivial because a relation is a set of tuples. The elements of sets must be unique, so it may in a relation, no two the same tuple type )
• Candidate key (also called candidate key or alternate key ): A minimal subset of the attributes of a super key, which enables the identification of tuples ( key candidates ⊆ super key ).
• Primary Key: The selected candidate key that is used in order for the mapping of relations. The values ​​of this key are used in referencing tables as a foreign key.

Formal definition

There is a certain relation schema R ( the table structure, that is, all columns) given. A subset S of the attributes of the schema R is called the key, if:

• Uniqueness: No occurrence of R ( none of the entries in the table) may contain two tuples (rows), in which the values ​​of S are the same.
• Definedness: All entries in the table must define the attributes of S, none of the entries may be null.

• Minimality: No proper subset of S already satisfies the condition of uniqueness.

Examples

A)

B )

C )

Candidate key

A candidate key ( engl. candidate key) is a minimal set of attributes that uniquely identifies the tuples ( records) of a relation. The formal definition is:

Here, the concept of full functional dependency - represented by - used. Here A is functionally dependent on full, which means:

In contrast to the super key here those sets of attributes are therefore only considered, which can no longer be reduced in size without losing their key property; it also says that they are minimal identifying. For example, the relations of the Introduction, the following key candidates arise:

From the list of super key words precisely those were selected which are minimal. Occasionally, the term candidate key is used, which is a literal translation of the English technical term candidate key.

Primary key and alternate key

To tell which of the candidate keys is preferred for identification of tuples in a relation which is selected from all candidate key is the primary key. The primary key is usually selected so that it is as small as possible, ie as few attributes and includes a simple as possible data type. He should be stable over time, its values ​​should therefore throughout the life cycle of the affected tables do not change, as it is with delay (which in principle possible by so-called cascading, however, but often time-consuming is ) also changes to the appropriate foreign key values.

In addition, the selected primary key must in fact allow the unambiguous identification of the real objects which are represented by the tuples of the relation. If one chooses, for example, the combination of {name, birthday } as the primary key, so it thus determines also that there should not be two people of the same name that have the same birthday ( uniqueness, uniqueness ). With the introduction of surrogate keys ( artificial keys, such as a serial number), this problem is avoided in any case. For example relations from the introduction, provide the following primary key ( are key candidates ):

Under the condition that no surrogate keys are to be introduced, the decision is in the examples b ) and c) obsolete, because there is only one candidate key; therefore it must be used as a primary key. In example a) one opts for { ISBN } as the primary key, because this is the smallest key (he has, in contrast to { author, book title, ...} only one attribute ), also characterized the reality is reproduced accurately. If a primary key consists of several attributes, one also speaks of a compound primary key or a composite key. By selecting the primary key all the other candidate keys of the relation to be an alternative keys automatically. In our example, this would be relations:

Alternate key have the purpose that in the table uniqueness is enforced at all key candidates, not just the primary key. (This alternative keys are in principle suitable to be used in another relation as a foreign key reference ).

Secondary key

Secondary keys are attribute groups, which are often used to describe single and multiple tuple ( search term). So may come as a secondary key question about the zip code in an address table.

In the database, foreign keys are ( colloquially just " Indices") implemented by secondary indexes. A secondary index is an optional, additional search structure of a database, the tuple makes them more discoverable by searching the entire data set is avoided ( like the index of a book, can be found by the terms specifically ).

Secondary key may not necessarily be unique ( only the alternate key, which are also foreign keys automatically, are unique). However, foreign key (also not necessarily unique) are secondary keys, because they are used to describe data sets ( arrange, group, etc.).

Deputy key

It is possible that all the candidate keys of a relation consist of multiple attributes, or that all key candidates reflect the real conditions insufficiently. Of our examples, b) is a case in point. If you want to identify in the customer table, a person, you must always specify the same name and birthday. It is therefore often desirable to introduce an additional attribute that serves as the primary key: This is called a deputy key (English surrogate key). For example b) Here are common attributes such as " identity card number " or business own identification numbers such as " customer number " or a consecutive number would offer.

Foreign key

A foreign key is an attribute or combination of attributes of a relation that refers to a primary key (or candidate key ) of another or the same relation.

It serves as a reference between two relations, that is, it indicates which tuples of relations are content with each other. Examples of foreign key are the two attributes " superior " and " subordinate " from the example relation c ) the introduction: This is where the " personnel number " of employees is indicated in each case. But with such a number can be in everyday life rather little start; are much more important name, department, employment and similar information. Therefore, a further relation is very likely to exist, contains the attributes as { employee number, name, department, employment, ...}. This relation is also most likely have the primary key personnel { number }; So it makes sense to use personnel number as a foreign key.

Definition

Let R, S relations and the attribute set α of the primary key of R. If a compatible attribute set β of S with respect to a foreign key to be α, the values ​​of β subset of the primary key values ​​must be in α R. (see referential integrity)

A set of attributes is then compatible with another if the value ranges of the attributes involved are the same, ie dom ( α ) = dom ( β ).

Foreign key and relationship types

In the database world, a distinction different types of relationships between two relations R and S. The term " relation" is - equivalent to the table - for the better understanding. In the case of relational databases, the following relationship types are distinguished:

The cases 1 and 2 are implemented by S contains the primary key of R as a foreign key. In the case of the 1:1 relationship, this is also the primary key. For the n: m relationship you need, as in Example c ) above, a separate relation that obtains the primary key of both relations as foreign keys. Both sets of attributes together are the primary key of this " shortcut relation".

Note: The actual so-called cardinalities of these three relationship types are 1:1 ⇒ [0,1 ]: [ 0,1], 1: n ⇒ [1,1 ]: [ 0, * ] and n: m ⇒ [ 0, * ]: [ 0, * ]. The "* " means " any number ".

Any other uses of the term

The following terms are not key in the sense of relational databases:

• Search key: An attribute or combination of attributes of a relation, which is used as a search criterion. A search key is not necessarily also an identifying key. It can also qualify multiple records on the same key value, therefore.

Key can also be distinguished according to their category of derivation as natural or artificial keys.

• Voice key (also called the natural key ): A candidate key that is present in the tuple in a natural way. Such a key has therefore also in the real world, a meaning, such as " VIN " in the police approved car. In speaking key is to be noted that the key domain can break if the Domain field is not chosen wisely. How can sometime be too small in the registrations of new vehicles about five digit car numbers due to growth, which requires a corresponding reorganization of key terms. If an attempt is made to encode the key also speaking group assignments, key breakthroughs are very likely, since the number ranges are not used continuously. Moreover, such a practice also violates the normalization requirement, so a group assignment on an attribute field or even an N should: M mapping table are made. Often natural keys are useful for example, to sort the records in the order of their construction, or be mandatory requested by the customer. For example, follow the account numbers with the requirements of the accounting framework. In the interpretation of the key domain criteria such as planned cardinality, readability and handling by the user must be considered sufficient.

• Vice key (surrogate key ): An artificially created, in the tuple previously not occurring attribute that identifies the tuples of the relation, which is often used as a primary key. Trivial example: Ongoing document number. Surrogate keys are applied as in the OLAP technology where very broad, composite keys are recoded to a more compact, artificial Surrogatekey. In heterogeneous application systems Surrogatekey is awarded for a specific entity from marked this leading system. When records are passed to second via interface systems, they must in addition to the key attribute, which is used for example in a moving table, possibly also the associated entity are transferred with the master data to the second system.