Introduction to Keys in MySQL

Published On: 4 April 2023.By .
  • Data, AI & Analytics
  • Quality Engineering

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 OR
    • For multiple column
  2. Defining primary key using ALTER (if primary key doesn’t exists in the created table)
    • For single column
    • For multiple column
  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
    • For multiple column

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; 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,

  2. Defining Foreign Key using ALTER (if primary key doesn’t exists in the created table)
    • For single column

    • For multiple column

  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

    • For multiple column

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)
  2. Defining unique key using ALTER (if unique key doesn’t exists in the created table)
  3. DROP a Foreign Key from the table

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.

Related content

That’s all for this blog

Go to Top