Compound key

In database design, a compound key is a set of superkeys that is not minimal.

A composite key is a set that contains a compound key and at least one attribute that is not a superkey.

Example

Given table: EMPLOYEES {employee_id,firstname,surname}

Possible superkeys are:

{employee_id}

{employee_id,firstname}

{employee_id,firstname,surname}

{employee_id} is the only minimal superkey, which also makes it the only candidate key--given that {firstname} and {surname} do not guarantee uniqueness. Since a primary key is defined as a chosen candidate key, and only one candidate key exists in this example, {employee_id} is the minimal superkey, the only candidate key, and the only possible primary key.

The exhaustive list of compound keys is:

{employee_id,firstname}

{employee_id,surname}

{employee_id,firstname,surname}

The only composite key is {employee_id,firstname,surname} since that key contains a compound key ({employee_id,firstname}) and an attribute that is not a superkey ({surname}).

Another example might be an entity that represents the modules each student is attending at University. The entity has a studentId and a moduleCode as its primary key. Each of the attributes that make up the primary key are simple keys because each represents a unique reference when identifying a student in one instance and a module in the other.

In contrast, using the same example, imagine we identified a student by their firstName + lastName. In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, it is not a simple key, it is a combination of attributes used to uniquely identify a student. Therefore the primary key for this entity is a composite key.

No restriction is applied to the attributes regarding their (initial) ownership within the data model. This means that any one, none, or all, of the multiple attributes within the compound key can be foreign keys. Indeed, a foreign key may itself be a compound key.

Compound keys almost always originate from attributive or an associative entity (tables) within the model, but this is not an absolute.

See also

External links

This article is issued from Wikipedia - version of the 9/8/2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.