Keys in DBMS
Keys in DBMS:
We use keys in DBMS to identify every tuple in the table uniquely. There may be more than one tuple having the same attribute values so, to identify rows uniquely we use keys. A key is an attribute or a set of attributes that help us uniquely identify a record or a row of the data in a table.
We take an example to understand this concept more efficiently.
Student_ID | Student_Name | Student_Branch | Student_Email |
1. | John | C.S | john@xyz.com |
2. | Elly | I.T | ellydiff@xyz.com |
3. | Adam | C.S | adamnerd@xyz.com |
4. | Elly | Electronics | ellyuni@xyz.com |
We can see here that there are some rows having the same attributes as there are two students with the same name. In this case, there are two values Student_ID and Student_Email which can work as the keys. In this way, we can uniquely identify the rows of the table.
There are four main keys to DBMS.
1. Super Key
2. Candidate Key
3. Primary Key
4. Foreign Key
We will take one table to identify the keys. Below is the table is given to understand the key concept more efficiently.
S_ID | REG_ID | NAME | BRANCH | |
1. | CS-2019-37 | John | CS | john@xyz.com |
2. | CS-2018-02 | Adam | CS | adamcool@xyz.coom |
3. | IT-2019-01 | Adam | IT | adamnerd@xyz.com |
4. | ECE-2019-07 | Elly | ECE | elly@xyz.com |
Super Key:
The set of attributes that can uniquely identify all the rows of a table is known as a super key. Example: Here S_ID, S_ID + NAME, BRANCH + REG_ID, and NAME + REG_ID, and more combinations like these can work as super keys.
Candidate Key:
The minimum attribute value which can identify every row uniquely is known as a candidate key. Example: Here S_ID, REG_ID, and EMAIL can work as the candidate keys.
Primary Key:
A Primary Key is a key that can uniquely identify every row in a table, here we can see there are three candidate keys, In these candidate keys, any one of them can work as a primary key. Example: Here S_ID or REG_ID or EMAIL can be the primary key.
Foreign Key:
It is an attribute in a table that is used to define its relationship with another table. Using foreign keys helps us maintain data integrity for tables in relationships. We use more than one table here to know the relationship.
Also read, Database languages in DBMS