A DBMS key is an attribute or set of an attribute which helps you to identify
a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys helps you to uniquely
identify a row in a table by a combination of one or more columns in that table.
STUDENT_ID | NAME | ADDRESS |
---|---|---|
101 | Prayag | Ranchi |
102 | Rahul | Punjab |
103 | Pankaj | Delhi |
105 | Top | UK |
106 | Harry | US |
A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.
Super key is a set of columns on which all columns of the table are functionally dependent. It is a set of columns that uniquely identifies each row in a table.
Super key may hold some additional columns which are not strictly required to uniquely identify each row. Primary key and candidate keys are minimal super keys or you can say subset of super keys.
STUDENT ID | STUDENT ROLLNO | STUDENT NAME | STUDENT MARKS |
---|---|---|---|
101 | 1 | James | 68 |
102 | 2 | Sam | 73 |
103 | 3 | David | 94 |
104 | 4 | James | 73 |
Suppose we have a table that holds all the students in a college, and that table is called students information. The table has columns called STUDENT ID, STUDENT ROLLNO, STUDENT NAME, and STUDENT MARKS. Every student has his/her own STUDENT ID, STUDENT ROLLNO, so that value is always unique in each and every row.
it is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys.
A Candidate key is also known as a minimal Super key.
StudID | Roll No | First Name | Last Name | |
---|---|---|---|---|
1 | 11 | Prayag | Verma | [email protected] |
2 | 12 | Rahul | Raz | [email protected] |
3 | 13 | Pankaj | Jacker | [email protected] |
4 | 14 | Mukesh | Mugu | [email protected] |
5 | 15 | Rakesh | Ravi | [email protected] |
Primary key is a candidate key of the table selected to identify each record uniquely in table. Primary key does not allow null value in the column and keeps unique values throughout the column
Primary key can be defined as a set of one or more fields/columns of a table that uniquely identify a record in a database table. A record can be uniquely identified when the column which includes unique value like Employee_Id of an employee from an organization. It will not accept null values and duplicate values. Only one primary key can exist in a single table not more than one.
Example: Suppose a table consists of Employee data with fields Employee_Name, Employee_Address, Employee_Id and Employee_Designation so in this table only one field is there which is used to uniquely
identify detail of Employee that is Employee_Id.
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
In the below example the Stu_Id column in Course_enrollment table is a foreign key as it points to the primary key of the Student table.
Course_Id | Stu_Id |
---|---|
C01 | 101 |
C02 | 102 |
C03 | 101 |
C04 | 102 |
Stu_Id | Stu_Name | Stu_Age |
---|---|---|
101 | Prayag | 21 |
102 | Rakesh | 22 |
103 | Pankaj | 26 |
Note: Practically, the foreign key has nothing to do with the primary key tag of another table, if it points to a unique column (not necessarily a primary key) of another table then too, it would be a foreign key. So, a correct definition of foreign key would be: Foreign keys are the columns of a table that points to the candidate key of another table.
Key that consists of two or more attributes that uniquely identify any record in a table is called Composite key. But the attributes which together form the Composite key are not a key independentely or individually.
In the above picture we have a Score table which stores the marks scored by a student in a particular subject.
In this table student_id
and subject_id
together will form the primary key, hence it is a composite key.
Unique key is similar to primary key and does not allow duplicate values in the column. It has below differences in comparison of primary key:
The unique key can be defined as a set of one or more fields/columns of a table that have the capability to uniquely identify a record in the database table.
We can have other fields also in a table beyond primary key which is also able to uniquely identify the record. It can accept only one null value and it cannot have duplicate values in it.
All the keys which are not primary key are called an alternate key. It is a candidate key which is currently not the primary key. However, A table may have single or multiple choices for the primary key.
Lets take an example to understand the alternate key concept. Here we have a table Employee, this table has three attributes: Emp_Id, Emp_Number & Emp_Name.
emp_Id | Emp_Number | Emp_Name |
---|---|---|
E01 | 1214 | Prayag |
E22 | 2272 | Pankaj |
E23 | 3261 | Rakesh |
E44 | 4825 | Hazra |
{emp_Id} and {Emp_Number}
DBA (Database administrator) can choose any of the above key as primary key. Lets say Emp_Id is chosen as primary key.
Since we have selected Emp_Id as primary key, the remaining key Emp_Number would be called alternative or secondary key.