Key Attributes in DBMS with Example

Key is a data item which is used to identify a record or a value used to identify a record in a database is called a key. It helps uniquely to identify an entity from an entity set.Key allows us to identify a set of attributes that make it sufficient to distinguish entities from each other.

Example

Account number, employee number, customer number are used as key fields because they specifically identify a record stored in a database.

Different types of keys

The different types of keys in the database management system (DBMS) are as follows −

• Primary key

• Super key

• Candidate key

• Alternate key

• Artificial key

• Foreign key

Primary, super, candidate, alternate and artificial keys show the property of uniqueness whereas the foreign key shows referential integrity.

Let’s consider a table −

Candidate key

It is an attribute whose values uniquely identify each record of a table. A table can have more than one candidate key.

Candidate key satisfy some conditions which are as follows −

• Different records cannot be identical.

• Subset of the candidate key cannot be a key.

In the above student table, candidate keys are as follows −

• stdNo and branch, because they uniquely identify each record.

• But {stdNo, name} is not a candidate key because stdNo alone is candidate key

Primary Key

From many candidate keys, the database designer selects one candidate key for his database called as primary key. The primary key uniquely identifies each record in a table and must never be the same for two records.

Example

In the above table stdNo is the primary key.

Primary key has some properties as mentioned below:

• Uniqueness (two different records cannot be identical).

• NOT NULL (null value is not allowed).

• Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.

Primary key is underlined in a table to clearly identify.

For Example − stdNo

A single column primary key is called a simple key and a Multi-column primary key is called composite primary key.

Example

• stdNo is a simple primary key.

• {orderNo, productNo} is a composite primary key.

Alternate Key

Candidate keys which are not selected for primary keys are called alternate keys. That is, except for the primary key the remaining candidate keys are alternate keys.

For Example −

In the above student table stdNo is the primary key so the rest candidate key i.e branch is an alternate key.

Super key

It is the superset of the candidate key. The combine attributes where the first attribute always have uniqueness and second attribute may or may not have uniqueness.

Example

In the above table candidate keys are {stdNo},{branch}

Super keys are as follows −

• {stdNo, name}

• {stdNo, address}

• {branch, section}

• {stdNo} etc.

Super keys are formed by adding extra attributes to candidate keys.

Artificial key

It is used in the form of indexes. If the size of the database is large and no attribute can be specified as a key field then the index or artificial key is applied on the given database.

Foreign key

In a relation, the column whose data values correspond to the values of a key column in another relation is called a foreign key. In a relational database, the foreign key of a relation may be the primary key of another relation.