Home Education What are Keys in MySQL? Overview And Understanding

What are Keys in MySQL? Overview And Understanding

Direct Switch to Contents

  1. What are the Keys?
  2. Types of Keys in MySQL
  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
MySQL Keys

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 NameMovie YearActor NameActor age
Barfi2012Rabir Kapoor37
Biwi no. 11999Salman Khan54
2 States2014Arjun Kapoor34
Fan2016Shahrukh Khan54
Kabir Singh2019shahid kapoor39
Dabangg 32019Salman Khan54



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

Add Constraint Atler

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.

Composite Key

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.

Foreign 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_idE_nameE_nameAddress
1Amit10Delhi
2Lalit20Noida
Dept_idD_name
10Sales
20Marketing

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_idE_nameDept_id
1Amit10
Dept_idD_name
10Sales

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.

Keys In Mysql

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

1. Can a unique key be null?

Yes, it can have NULL for a single time just like any other value, as NULL is a permissible value

2. Can we have two NULL values ?

With unique key you cannot insert multiple nulls
But with foreign key it can be possible as it is used to link two tables together.

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

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

RELATED ARTICLES

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

Top 10 Network Marketing Companies in India | Future of MLM Business

Everybody knows about marketing that it is an essential part of a business. It is essential to promote our product to others...

Recent Comments