What are the different types of keys in a database?
Keys in a database are a vital and integral part of a table structure. The keys ensure the unique identification of records, ensure referential integrity and define relationships between tables.
There are majorly three types of keys candidate key, primary key and foreign key. Apart from these there are other types of keys as well, and a clear understanding of those is very important in the design of any database schema.
Go through the following explanation and illustrations to get your concepts about the keys strengthened. We hope to clarify most of your doubts regarding this topic once and for all. If you have any further questions please don’t hesitate to leave your queries in the comment section. We will make sure all your questions are answered at the earliest.
Super key: A super key is any combination of fields within a table that uniquely identifies each record within a table.
Primary key, Unique key, Candidate key are all subsets of super key.
Example: Consider the relation student as
In the above table following are the super keys
Regn No, Roll No
Regn No, Roll No, Course Id
Regn NO, Roll No, Address
Regn No, Roll No, Course Id, Address
All the above combinations identify the records uniquely hence are called super keys.
Candidate key: Candidate keys are special subsets of super keys that don’t have extraneous information. It is a field or a combination of fields that uniquely identify each record in the table.
Candidate keys are candidates for becoming a primary key hence the name.
Example: Referring to the same table
Student (Regn No, Roll No, Course Id, Address)
Following are the candidate keys for the table
Note that only two out of six super keys have qualified to be candidate keys. The reason is that some keys contain more than the minimum necessary information to identify the records. E.g. The combination Regn No, Roll No, Course Id is rejected to be a candidate key as Roll No or Regn No alone can by themselves identify the record uniquely, there is no need of extra fields with them.
Primary key: A primary key is a set of one or more fields of a table that uniquely identify a record in a table.
Primary key field cannot accept null, duplicate values.
Moreover, only one candidate key can be primary key.
According to the above three definitions, it is clear that the features of all the three types is to identify the records uniquely in a table. Then where is the difference?
The difference is clear from their relationship.
Alternate Key or Secondary Key: An alternate key is a field or set of fields which can act as primary key. Basically it is a candidate key which is not currently a primary key.
A table may have multiple candidate keys which means multiple choices for primary key. The one selected out of these becomes primary key and others are called alternate key or secondary key.
Example: Student (Regn No, Roll No, Course Id, Address)
Primary Key: Roll No
Alternate Key: Regn No
In the above table both the fields Regn No and Roll No can act as primary key as they are unique for every student, but we have currently taken Roll No as primary key so the field Regn No can be called alternate key. In your table you make take Regn No as primary key then Roll No becomes alternate key.
Foreign Key: A foreign key is a primary key from one table that appears as a field in another. This is used to establish or enforce the relationship between the two tables.
Example: Consider the Following two tables
Student (Regn No, Roll No, Course Id, Address)
Course (Course Id, Course Name, Faculty-in-Charge)
The Course Id is the primary key in the Course table and the same field appears in the Student table and acts as a foreign key for it.
But why do we need a foreign key?
The foreign key relationship of Student table with Course table on the basis of Course Id ensures that only the values already present in the Course table can be allowed in the Course Id field of Student table to avoid invalid values, this is called referential integrity.
Hence, Foreign keys ensure referential integrity.
Surrogate Key: Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table. The surrogate key is not derived from application data. The surrogate is internally generated by the system and is invisible to the user or application.
Example: The SEQUENCE feature in Oracle can be used to generate unique keys to identify records in a table. ROWID in Oracle acts as a surrogate key.
Some important features of a surrogate key are:
- The keys have no semantic or business meaning
- The value is system generated
- Not visible to user or application
- Cannot be manipulated by the user
Surrogate key is also known as Natural key.
Composite key: It is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.
What is a compound key? Is it different from composite key?
A compound key is a key that consists of two or more attributes that uniquely identify a record. Each attribute that makes up the compound key is a simple key in its own right.
This is often confused with a composite key whereby even though this is also a key that consists of two or more attributes that uniquely identify a record, but at least one attribute that makes up the composite key is not a simple key in its own right.
What is Unique key? What is the difference between Unique key and Primary key?
The primary key is used to identify a row uniquely in a table. Furthermore, they can’t be null. A table can have only have one PK. All tables in your database should have a PK (although this is not enforced by most DBMS). Primary Key can span multiple columns.
Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns.