What are the Keys in DBMS?
A tuple (or row) in a relation (or table) can be uniquely identified by one or more properties, known as a key, in a database management system (DBMS). A relational database’s many tables and columns can be linked to one another via keys. Key values are the specific values included within a key.
Why are the Keys Required?
The definitions of various types of integrity constraints all make use of keys. A database table represents a group of documents or occasions for a certain relation. There might be countless numbers of these records, some of which might be duplicates.
Different Types Of Keys in DBMS
There are following 10 important keys in DBMS:
- Super key
- Candidate key
- Primary key
- Alternate key
- Foreign key
- Partial key
- Composite key
- Unique key
- Surrogate key
- Secondary key
Super Key:
- Each tuple in the provided relation can be uniquely identified by a collection of characteristics known as a super key.
- Super keys can have any amount of properties; there is no set limit.
- The amount of properties that make up a super key can thus be infinite.
Example:
The following student schema is an example.
Student ( roll, name, sex, age, address, class, section )
Examples of super keys are shown below since each set may uniquely identify each student in the Student database.
- ( roll, name, sex, age, address, class, section )
- ( class, section, roll )
- (class, section, roll, sex )
- ( name, address )
NOTE:
Although they may not all be necessary, each one of the characteristics in a super key can be used to uniquely identify each tuple in the given relation.
Candidate Key:
- A candidate key is a subset of the super key, which is the superset of all the qualities that may be used to uniquely identify a table (described in the preceding section).
- A candidate key is a subset of a super key set in which a candidate key is the only key that lacks any redundant attributes. We must examine the super key set to choose the candidate keys from the set of super keys.
Example:
Take into account the following student schema:
Student ( roll, name, sex, age, address, class, section )
The possible key examples are included below because each set has the minimum properties needed to uniquely identify each student in the Student table.
- ( class, section, roll )
- ( name, address )
NOTE:
- Each tuple can be uniquely identified using any or all of the qualities in a candidate key.
- Each tuple cannot be uniquely identified when a candidate key has one or more attributes removed.
- Candidate key values must always be distinct.
- Candidate key values can never be NULL.
- In a relation, more than one candidate key may exist.
- Prime qualities are those that are present in at least one candidate key.
Primary Key:
A primary key is the bare minimum collection of properties in a database with the job of uniquely identifying the rows, or tuples, in the specified specific table.
For example:
The registration number column is unique when we save the student registration information in the database, thus we give it the primary key. Additionally, we put the employee Id of the database as the primary key for an employee table.
Let’s look at it in practice:
The STUDENT_DETAILS table is shown below, and its specified properties are Roll_no, Name, and Marks.
Since each student is given a different roll number in every organization, it is known that the Roll_no attribute is the one that can uniquely identify the other two characteristics of the table out of these three. Therefore, we can specify the Roll_no column’s primary key constraint.
NOTES:
- Primary key values can never be NULL.
- Primary key values must always be distinct.
- There is no way to update the main key values since they can never be altered.
- When adding a record, the primary key value must be specified.
- There can only be one primary key per relation.
Foreign Key:
- When an attribute X’s values depend on the values of another attribute Y, that attribute is referred to as a foreign key.
- Only those values that the attribute ‘Y’ assumes are permissible for the attribute ‘X’.
- The connection in question is referred to as the referenced relation because attribute ‘Y’ is contained in it.
- The referenced connection is the one in which attribute ‘X’ is present.
- ‘Y’ might exist in the same table or a different table.
Example:
To further comprehend the idea of working with a foreign key, let’s talk about an example.
First, let’s look at two tables: Customers and Orders, each of which has the features shown in the table structure. To understand the Example of a Foreign Key, there are dummy data or information in both tables.
You can see from the tables above that one characteristic, CustomerID, is shared by the two tables. However, there is one difference in the key constraints for the two tables. CustomerID, a field in the Customers table, serves as the primary key since it identifies all other fields in the database in a singular way. On the other hand, as you can see, CustomerID serves as a primary key (PK) property for the Customers table, making it a foreign key attribute for the Orders table. Thus, the CustomerID property signifies that the Orders and Customers columns are related to one another. You can see the following structure of the connections between the two tables in the image displayed below.
NOTES:
- A foreign key refers to the table’s main key.
- Only values that are present in the primary key of the referenced relation can be used as foreign key values.
- A foreign key may go by a different name than a primary key.
- A foreign key may accept the value NULL.
- A foreign key can be as unique as it wants to be.
- In reality, a foreign key is frequently not unique.
- The master table or primary table may also be referred to as a referenced relation.
- Referencing relationships are sometimes referred to as foreign tables.
Unique Key:
- A key that has the following characteristics is unique:
- It is exclusive to each record in the table.
- Once a value has been assigned, it cannot be updated; it is non-updatable.
- It might contain a NULL value.
Example:
- The Adhaar Card Numbers are the greatest illustration of a unique key.
- Every Indian citizen has a different Adhaar Card Number, as shown in the table below.
- The duplicate copy always has the same number as the original if it is lost and a new one is issued.
- Therefore, it cannot be updated.
- Few people may not have received their Adhaar cards, in which case their value is NULL.
Surrogate Key:
- A substitute key has the qualities listed below:
- It is exclusive to each record in the table.
- It can be updated.
- It must have some value because it cannot be NULL.
Example:
Mobile The proportion of pupils in a class who all have mobile phones.
Alternate Key:
After the primary key is implemented, candidate keys that are not utilized or implemented are referred to as alternate keys. Alternate keys are unimplemented candidate keys.
Secondary Key:
For indexing, a secondary key is necessary for more accurate and quick searching.
Partial Key:
A partial key is a key that does not allow all of the table’s records to be uniquely recognized.
However, the partial key may be used to choose several related tuples from the table.
Composite Key:
A composite key is a main key that consists of numerous properties rather than simply one.
