KEYS

MySQL is a relational database, and in a relational database, a key is defined as a column or a group of columns which are used to uniquely locate records in a table of a Relational Database.

We can use the keys to relate  and extract data from the database more precisely whenever required.

There are various MySQL keys,

1. Primary Key
2. Foreign Key
3. Unique Key
4. Candidate Key
5. Super Key

the most basic and important keys amongst all is the Primary key.

Primary Key

It is the very first key that is use to identify one and only one instance of a table uniquely.

A table can have only one primary key. Primary key must contain UNIQUE values; and it doesn’t allows NULL value.

The Primary Key can consists of a single or multiple columns(fields).

There are multiple ways to define the primary Key for a table;

1. Defining primary key using CREATE(while creating a new table)
   • For single column
     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, PRIMARY KEY (Employee_ID) );

     1 2 3 4 5 6 7

     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, PRIMARY KEY (Employee_ID) );

     OR
     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0 );

     1 2 3 4 5 6

     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0 );

   • For multiple column
     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, PRIMARY KEY (Employee_ID ,Employee_License) );

     1 2 3 4 5 6 7

     CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, PRIMARY KEY (Employee_ID ,Employee_License) );

2. Defining primary key using ALTER (if primary key doesn’t exists in the created table)
   • For single column
     ALTER table Employee ADD PRIMARY KEY (column name);

     1	ALTER table Employee ADD PRIMARY KEY (column name);

   • For multiple column
     ALTER table Employee ADD constraints PK_Employee PRIMARY KEY (column name1, column name 2);

     1	ALTER table Employee ADD constraints PK_Employee PRIMARY KEY (column name1, column name 2);

3. DROP a primary key from the tableIf you want to drop a primary key constraint from the table , then you can use the following MySQL;
   • For single column
     ALTER TABLE Employee DROP PRIMARY KEY;

     1	ALTER TABLE Employee DROP PRIMARY KEY;

   • For multiple column
     ALTER TABLE Employee DROP CONSTRAINT PK_Employee;

     1	ALTER TABLE Employee DROP CONSTRAINT PK_Employee;

Foreign Key

The foreign key allows us to ensure the referential integrity by placing constraint on data in the related table. It is the column of a table i.e used to point to the primary key of the another table.

A foreign key is a field (or collection of fields) in one table , that refers to the primary key in another table.

The table which contains the foreign key is called the child table , and the table which have the primary key is known as parent table.

1. Defining Foreign Key using CREATE(while creating a new table)For example, We have two tables that are Employee and Department table. You can create both table as given below;
   CREATE TABLE Department( department_id INT PRIMARY KEY, department_name VARCHAR(25) );

   1	CREATE TABLE Department( department_id INT PRIMARY KEY, department_name VARCHAR(25) );

   CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, FOREIGN KEY(department_id) REFERENCES Department(department_id) );

   1 2 3 4 5 6 7

   CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, FOREIGN KEY(department_id) REFERENCES Department(department_id) );

   Here department_id is the foreign key in the employee table while it is primary key in the department table.

   To allow naming of a Foreign Key constraint, and for defining a Foreign Key on multiple columns,

   CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, CONSTRAINT FK_dept_id FOREIGN KEY(department_id) REFERENCES Department(department_id) );

   1 2 3 4 5 6 7

   CREATE table Employee ( Employee_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0, CONSTRAINT FK_dept_id FOREIGN KEY(department_id) REFERENCES Department(department_id) );

2. Defining Foreign Key using ALTER (if primary key doesn’t exists in the created table)
   • For single column
     ALTER TABLE Employee ADD FOREIGN KEY (department_id) REFERENCES Department(department_id);

     1	ALTER TABLE Employee ADD FOREIGN KEY (department_id) REFERENCES Department(department_id);

   • For multiple column
     ALTER TABLE Employee ADD CONSTRAINT FK_dept_id FOREIGN KEY (department_id) REFERENCES Department(department_id);

     1 2	ALTER TABLE Employee ADD CONSTRAINT FK_dept_id FOREIGN KEY (department_id) REFERENCES Department(department_id);

3. DROP a Foreign Key from the tableIf you want to drop a Foreign Key constraint from the table , then you can use the following MySQL;

• • For single column
    ALTER TABLE Employee DROP FOREIGN KEY FK_dept_id;

    1	ALTER TABLE Employee DROP FOREIGN KEY FK_dept_id;

  • For multiple column
    ALTER TABLE Employee DROP CONSTRAINT FK_dept_id;

    1	ALTER TABLE Employee DROP CONSTRAINT FK_dept_id;

Unique Key

A group of one or more columns of a table that can uniquely identify a tuple/record is known as a unique key.
it prevents from storing duplicate value in two records in a column. Unique key can have NULL value.

A primary key automatically has a unique constraint.

we can have multiple UNIQUE KEY constraints in a single table , but we can only have one PRIMARY KEY constraint in a single table

There are multiple ways to define the Unique Key for a table;

1. Defining unique key using CREATE (while creating a new table)
   CREATE table Employee ( Employee_ID INT NOT NULL UNIQUE, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0 );

   1 2 3 4 5 6

   CREATE table Employee ( Employee_ID INT NOT NULL UNIQUE, Employee_Name VARCHAR(30) NOT NULL, Employee_License INT NOT NULL, Employee_Department VARCHAR(15) NOT NULL DEFAULT 0 );

2. Defining unique key using ALTER (if unique key doesn’t exists in the created table)
   ALTER table Employee ADD UNIQUE(column name);

   1	ALTER table Employee ADD UNIQUE(column name);

3. DROP a Foreign Key from the table
   ALTER TABLE Employee DROP CONSTRAINT Employee_ID ;

   1	ALTER TABLE Employee DROP CONSTRAINT Employee_ID ;

Candidate key

Candidate key is a column or set column that can uniquely identify a tuple/record in a table. A table can contain multiple keys that can uniquely identify the record so except primary key remaining key are considered as candidate key.

For example, In the EMPLOYEE table, employee_id, employee_license are the keys which are unique for each Employee. Here Employee_ID is the best suitable for the Primary key so the employee_license is considered as the candidate key.

Super Key

Super key is set of fields/columns that can uniquely identify every tuple/row in a table.

For Example, Employee_ID , (Employee_ID , Employee_Name ), Employee_License, (Employee_ID , Employee_Department) all keys can be super key.

confused? so Employee_ID , it is very simple that every employee have unique employee id.
(Employee_ID , Employee_Name ), here Employee_Name can be same for two records but their Employee_ID can’t be same hence this combination is also a key.

Employee_License, will also be unique so it is also a key.
Similarly (Employee_ID , Employee_Department), here Employee_Department can be same for two records but their Employee_ID can’t be same hence this combination is also a key.