EnggPedia - The Engineering Encyclopedia

Wed03292017

Last update06:04:15 AM

Font Size

Profile

Menu Style

Cpanel
Back Computer Algorithms Keys & Types of Keys in Database - Super, Candidate, Primary, Foreign etc

Keys & Types of Keys in Database - Super, Candidate, Primary, Foreign etc

Definition:

A key is an attribute or a set of attributes in a relation that identifies a tuple in a relation. The keys are defined in a table to access or sequence the stored data quickly and smoothly. They are also used to create relationship between different tables.

Types of Keys

Following are the different types of keys.

  1. Super key.
  2. Candidate key.
  3. Primary Key.
  4. Alternate Key.
  5. Composite key.
  6. Foreign key.

Super Key

A super key is an attribute or combination of attributes in a relation that identifies a tuple uniquely within the relation. A super key is the most general type of key. For example, in a relation STUDENT consists of different attributes like RegistrationNo, Name, FatherName, Class and Address. The only attribute that can uniquely identify a Tuple in a relation is RegistrationNo. The Name attribute cannot identify a tuple because two or more students may have the same Name. Similarly FaththeName, Class and Address can not be used to identify a tuple. It means that RegistrationNo is the super key for the relation. Any combination of attributes with the super key is also a super key. it means any attribute or set of attributes combined with the super key Registrationno will also become a super key. A combination of two attributes {RegistrationNo, Name} is also a super key. This combination can also be used to identify a tuple in a relation. Similarly {RegistrationNo, Class} or {RegistrationNo, Name, Class} are super keys.

Candidate Key

A candidate key is a super key that contains no extra attribute. It consists of minimum possible attributes. A super key like {RegistrationNo, Name} contains an extra field Name. It can be used to identify a tuple uniquely in the relation, But it does not consist of minimum possible attribute as only RegistrationNo can be used to identify a tuple in a relation. It means that {RegistrationNo, Name} is a super key but it is not a candidate key because it contains an extra field. On the other hand, RegistrationNo is a super key as well as candidate key.

Primary Key

A primary key is a candidate key that is selected by the database designer to identify tuples uniquely in a relation. A relation may contain many candidate keys. When the designer selects one of them to identify a tuple in the relation, it becomes a primary key. It means that if there is only one candidate key, it will be automatically selected as primary key.

Some most important points about a primary key are.

  1. A relation can have only one primary key.
  2. Each value in primary key attribute must be unique.
  3. Primary key can not contain null values.

Suppose a relation STUDENT contains different attributes such as RegNo, Name and Class. The attribute RegNo uniquely identifies each student in a table. It can be used as primary key for this table. The attribute Name can not uniquely identify each row because two students can have same names. It can not be used as a primary key.

Alternate Key

The candidate keys that are not selected as primary key are known as alternate keys. Suppose STUDENT relation contains different attributes such as RegNo, RollNo, Name and Class. The attributes RegNo and RollNo can be used to identify each student in the table. If Regno is selected as primary key then RollNo attribute is known as alternate key.

Composite Key

A primary key that consists of two or more attributes is known as composite key.

Foreign Key

A foreign key is an attribute or set of attributes in a relation whose values match a primary key in another relation. The relation in which foreign key is created is known as Dependent Table or Child Table. The relation to which the foreign key refers is known as Parent Table. The key connects to another relation when a relationship is established between two relations. A relation may contain more than one foreign keys.