Keys are very important part of Relational database model. They are used to establish and identify relationships between tables and also to uniquely identify any record or row of data inside a table.
A Key can be a single attribute or a group of attributes, where the combination may act as a key.
keys are preferred and they are important part of the arrangement of a table. Keys make sure to uniquely identify a table’s each part or record of a field or combination of fields. A database is made up of tables, which (tables) are made up of records, which (records) further made up of fields.
Various Keys in Database Management System (DBMS)
Types of Keys in Database Management System: Each key which has the parameter of uniqueness is as follows:
- Super key
- Candidate key
- Primary key
- Composite key
- Secondary or Alternative key
- Non- key attribute
- Non- prime attribute
- Foreign key
- Simple key
- Compound key
- Artificial key
The detailed explanation of all the mentioned keys is as follows:
1. Super key
Super Key is a set of properties within a table; it specially identifies each record in a table. Candidate key is a unique case of super key.
For example: Roll No. of a student is unique in relation. The set of properties like roll no., name, class, age, sex, is a super key for the relation student.
2. Candidate keys
Candidate keys are the set of fields; primary key can be selected from these fields. A set of properties or attributes acts as a primary key for a table. Every table must have at least one candidate key or several candidate keys. It is a super key’s subset.
- The above fields of a candidate key uniquely identify a student.
- It has the properties like – Being unique and Parameter of irreducibility.
3. Primary key
The candidate key which is very suitable to be the main key of table is a primary key.
- The primary keys are compulsory in every table.
- The properties of a primary key are:
- Model stability
- Occurrence of minimum fields
- Defining value for every record i.e. being definitive
- Feature of accessibility
4. Composite key
Composite Key has two or more properties which specially identifies the occurrence of an entity.
- In the above example the customer identity and order identity has to combine to uniquely identify the customer details.
5. Secondary or Alternative key
The rejected candidate keys as primary keys are called as secondary or alternative keys.
6. Non-key Attribute
The attributes excluding the candidate keys are called as non-key attributes.
7. Non-prime Attribute
Excluding primary attributes in a table are non-prime attributes.
Non prime attributes
8. Foreign key
Generally foreign key is a primary key from one table, which has a relationship with another table.
9. Simple key
Simple keys have a single field to specially recognize a record. The single field cannot be divided into more fields. Primary key is also a simple key.
- Example: In the below example student id is a single field because no other student will have same Id. Therefore, it is a simple key.
10. Compound key
Compound key has many fields to uniquely recognize a record. Compound key is different from composite key because any part of this key can be foreign key but in composite key its part may or may not be a foreign key.
11. Surrogate/Artificial key
Surrogate key is artificially generated key and its main purpose it to be the primary key of table. Artificial keys do not have meaning to the table.
There are few properties of surrogate or artificial keys.
They are unique because these just created when you don’t have any natural primary key.
They are integer values.
One cannot find the meaning of surrogate keys in the table.
End users cannot surrogate key.
Surrogate keys are allowed when
- No property has the parameter of primary key.
- The primary key is huge and complex.
Example: Table which has the details of the student has primary key but it is large and complex. The addition of row id column to it is the DBA’s decision, where the primary key is row id.