What are Keys in MySQL? Overview And Understanding

Direct Switch to Contents

1. What are the Keys?
2. Types of Keys in MySQL
   • Super Key
   • Primary Key
   • Candidate Key
   • Alternate Key
   • Foreign Key
   • Compound Key
   • Composite Key
   • Surrogate Key
3. Conclusion
4. FAQs

What are the Keys?

Keys are the attributes or set of attributes that are used to uniquely identify a row (tuple) or can say a record in the table. It allows us to find the relation between two tables in a DATABASE. Keys help uniquely identify a row in a table by a combination of one or more columns in that table. Here we will learn each key one by one.

Types of Keys in MySQL

There are several types of key in MySQL are as follow:

1. Super Key
2. Primary Key
3. Candidate Key
4. Alternate Key
5. Foreign Key
6. Compound Key
7. Composite Key
8. Surrogate Key

Super Key

The super key can be defined as a set of one or more than one key that can be used to identify a record/data uniquely in a table. This key includes only those fields which include unique value as if we take an example of Employee than Employee_Id will be the field which includes unique value and it becomes easy to identify the employee from Employee_Id field.

For Example -Keys that can be the subset of Super Key are Primary key, Unique key, and Alternate key. As right now we don’t know about these keys so further we will discuss these keys.

Here we will learn some of them.

PRIMARY KEY:

Primary Key is a column or set of columns in a table that uniquely identifies each row in that Table.
A primary key must contain Unique and NOT NULL values.
Here not null means it can not be void there must be a unique value, can not let blank there.
A table in a database can not have more than one primary Key.
Adding primary key while creating the Table-:
Create Table Movies
-> (
-> Movie_name varchar(20),
-> Movie_year int(2),
-> Actor_name varchar(20),
-> Actor_age int(2),
-> PRIMARY KEY (Movie_name)
-> );
Here Primary Key is Movie Name so no two movies can not be same.

Adding the primary key to the existing column of Table-:
Syntax: ALTER TABLE <Table Name>
ADD Constraint <Constrains_name>
PRIMARY KEY (Column name);

We have created a Table FILMS in previous blogs. So here we will add Primary Key to the Column Move_name of the Table.

Movie Name	Movie Year	Actor Name	Actor age
Barfi	2012	Rabir Kapoor	37
Biwi no. 1	1999	Salman Khan	54
2 States	2014	Arjun Kapoor	34
Fan	2016	Shahrukh Khan	54
Kabir Singh	2019	shahid kapoor	39
Dabangg 3	2019	Salman Khan	54

>Alter Table Films
Add Constraint <Movie_Name_pk>
Primary Key (Movie_name);

Here we added the Primary key to the movie_name Column and
One thing you guys definitely noticed that here. After the DESC command to describe the table Contents and its Attributes. Movie_name field is showing a PRI which means it is now a primary Key.

Composite Primary Key:

A composite Primary key can have multiple columns in a table whose combination must always be unique.

Here we Name and Address act as a Composite Primary key.
According to the definition of the primary key, a table can have only one primary key. Here Combination of Name And address is one Primary key.
Syntax: Alter Table
Add Constraint< Constrains name >
Primary Key (Column Names);

To Drop A Primary Key of a column from a Table.
When we need to delete the primary key from the column of a table then we use drop command.
Syntax: Alter Table <Table Name> Drop primary Key

Foreign Key:

The foreign key is used to maintain the referential integrity between the two tables.
It points to the primary key of another table.
It denotes the parent-child relationship.
A child can not be inserted unless the parent exists and parents cant not be deleted if a child exists.
It can set to null value but not for the primary key.

Syntax of adding Foreign key:
Alter Table <table name> add Constraint <table name>
Foreign Key( Column Name 1) references (Column Name 2);

Both column must be from different table and must be primary keys.

Important Clauses while Creating Foreign Key- On DELETE And UPDATE Commands :
What is On DELETE Cascade?
When we delete the parent row (what will happen to child row), the child row will be cascaded. It means dependent child records will also be deleted.
Here is the syntax of On delete cascade
Alter Table <table name> add Constraint <Constraint Name>
Foreign Key( Column Name 1) references (Column Name 2) ON DELETE CASCADE;
Let us take two tables emp and dept having their primary keys.

E_id	E_name	E_name	Address
1	Amit	10	Delhi
2	Lalit	20	Noida

Dept_id	D_name
10	Sales
20	Marketing

Now if here we apply on delete cascade
Let us see what will happen
Alter Table emp add Constraint emp_dept_fk
Foreign Key( Dept_id) references (Emp_id) ON DELETE CASCADE;
Now performing Delete Operation on parent Table.
Delete from dept where dept_id = 20;
Now look at both tables.

E_id	E_name	Dept_id
1	Amit	10

Dept_id	D_name
10	Sales

Here are shows that delete operation is performed. And its corresponded child table record is also deleted.
If we perform only delete operation without performing ON CASCADE DELETE operation then those delete operation gives an error.

Candidate Key

Candidate Key can be defined as a set of one or more fields/columns that can identify a record uniquely in a table like a primary key or we can also say that other fields than a primary key which can become the primary key and a table can have more than one candidate key. Each candidate key can work as a primary key if required in any case.
For Example:- Suppose a table consists of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation.Employee_PANNo and Employee_PhoneNo in this table Employee_PhoneNo and Employee_PANNo are Candidate Keys as these two fields can also work as a candidate keys.

Alternate key

An alternate key can be defined as a key that can be work as a primary key if required. We can also understand this key as a candidate for the primary key as a candidate key but right now it is not a primary key.
For Example:- Suppose a table consists of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation, and Employee_PhoneNo, in this case, Employee_PhoneNo can be the alternate key as it is also suitable to identify the record uniquely but right now it is not the primary key.

Surrogate key

A surrogate key is a unique identifier used in databases for a modeled entity or an object. It is a unique key whose only significance is to act as the primary identifier of an object or entity and is not derived from any other data in the database and may or may not be used as the primary key.

Conclusion

Primary and foreign keys are a way in which to constrain related data together to ensure data in your database remains consistent and to ensure no redundant data is in the database as a result of deleting a table or row in one table that affects data in other tables that may perhaps rely on that information. It can cause both data integrity problems as well as problems with your application that makes use of such a database.

Well, even though the column set as a foreign key may have duplicate data in multiple rows, it is still relying on the unique information in the primary key. If there were non-unique rows in the primary key column, there would not be a use for a foreign key because you would be mapping one row in the foreign key to two rows in the primary key.

FAQs

Next Blog |What are the Functions in MySQL? Some Built-in Functions

Previous Blog | What are the Queries and Subquery in MySQL Database?