Keys in DBMS
What are the Keys in DBMS?
A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Individual values in a key are called key values.
Why are the Keys Required?
A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated.
Types of Keys in DBMS
- Primary Key
- Candidate Key
- Super Key
- Foreign Key
- Composite Key
- Alternate Key
- Unique Key
Let’s look at each of them separately.
1. Primary Key
Primary key in DBMS is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key.
Rules for defining Primary key:
- Two rows can’t have the same primary key value
- It must for every row to have a primary key value.
- The primary key field cannot be null.
- The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
example :
Here, “College ID No.” can be termed as a primary key because it can uniquely identify all the details of the professors easily. Two professor can have same name that is why name is not a primary key.
2. Super Key
Super Key is the set of all the keys which help to identify rows in a table uniquely. This means that all those columns of a table than capable of identifying the other columns of that table uniquely will all be considered super keys.
Super Key is the superset of a candidate key . The Primary Key of a table is picked from the super key set to be made the table’s identity attribute.
example :
Here, super key can be a single attribute such as “(Roll No.)” or a collection of multiple attributes such as “(Name, Mobile No)” which can help in identification of record of every student.
3. Candidate Key
Candidate keys are those attributes that uniquely identify rows of a table. The Primary Key of a table is selected from one of the candidate keys. So, candidate keys have the same properties as the primary keys explained above. There can be more than one candidate keys in a table.
There can be more candidate keys than just one for any table, but they can never be empty. Every candidate key carries unique information and value. Besides these characteristics, a combination of attributes also works as a set of candidate keys.
example :
Here, Voter Id No, Passport No & PAN No can be termed as candidate keys because each of these can uniquely identify all the records present in the table.
Note: Out of Voter Id No, Passport No & PAN No, any of can be selected as a primary key because of their uniqueness.
4. Alternate Key
As stated above, a table can have multiple choices for a primary key however it can choose only one. So, all the keys which did not become the primary Key are called alternate keys.
example:
5. Foreign Key
Foreign Key is used to establish relationships between two tables. A foreign key will require each value in a column or set of columns to match the Primary Key of the referential table. Foreign keys help to maintain data and referential integrity.
Foreign keys are essential for maintaining a difference between two entities that might be linked with the same information but do not share similar information. In such cases, the tables are linked to maintaining a connection but do not entirely work as a replacement for each other. For instance, any individual working for the marketing department might have marketing department information in its employee table, but that does not mean the table can be similar to the department table held by the marketing department.
example :
Consider two relations TABLE_1(Roll No, Name, Marks) and TABLE_2(Roll No, Address, Age) of students of a school. Roll No being the common attribute will act as a key for relation TABLE_1 and can reference to Roll No attribute of relation TABLE_2 with the help of which any tuple can be retrieved from the tables.
6. Composite Key
A composite Key is a set of two or more attributes that help identify each tuple in a table uniquely. The attributes in the set may not be unique when considered separately. However, when taken all together, they will ensure uniqueness. The ‘concatenated key’ is another name for a composite key.
example :
Consider the table of student having their details. If we wish to choose a composite key, it can be done as
7. Unique Key
Unique Key is a column or set of columns that uniquely identify each record in a table. All values will have to be unique in this Key. A unique Key differs from a primary key because it can have only one null value, whereas a primary Key cannot have any null values.
Among the seven types of keys available in the DBMS, a few other types of keys in SQL are also accessible. The key type is called Artificial Keys. An artificial has no relevance or meaning to the business but is often used to tackle conflicting data management situations. For example, if there is no attribute that comprises all required primary key properties or if the primary keys are complex.
example :
