What are The AQL keys with Example

aimtocode

Introduction to Database Keys:

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

Why do we need a Key?

  1. To create relationships between two tables.
  2. To maintain uniqueness in a table.
  3. To keep consistent and valid data in database.
  4. Might help in fast data retrieval by facilitating indexes on column(s).

Types of Keys in DBMS

  1. Super Key
  2. Candidate Key
  3. Primary Key
  4. Foreign Key
  5. Composite Key
  6. Unique Key and
  7. Alternate Key etc.

1. Super Key

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.



2. Candidate Key

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.

Properties of Candidate key:

  • It must contain unique values
  • Candidate key may have multiple attributes
  • Must not contain null values
  • It should contain minimum fields to ensure uniqueness
  • Uniquely identify each record in a table
StudID Roll No First Name Last Name Email
1 11 Prayag Verma support@aimtocode.com
2 12 Rahul Raz rahulraz@gmail.com
3 13 Pankaj Jacker pangu@gmail.com
4 14 Mukesh Mugu mugu@gmail.com
5 15 Rakesh Ravi rakeshravi@gmail.com



Primary Key

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.




5. Foreign Key:

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.

Example:

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_enrollment table:

Course_Id Stu_Id
C01 101
C02 102
C03 101
C04 102

Student table:

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.



Composite Key:

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.



6. Unique 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:

  1. it allows one null value in the column.
  2. by default, it creates a nonclustered index on heap tables.

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.



7. Alternate Key

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

There are two candidate keys in the above table:

								
 {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.

aimtocode