Working with Keys in MySQL

INTRODUCTION

In this tutorial, I am going to explain about keys in MySQL with examples. Without wasting time, let’s start.

In MySQL, a key is a data item that identifies a record exclusively. In other terms, the key is a group of columns used to uniquely define a record in a table. It is used to retrieve or extract rows from a table as needed. Keys have many forms of constraint, such as columns, which cannot hold repeated values or null values.

Types of Keys

MySQL supports the following types of keys. Let’s see.

Super Key
Candidate Key
Primary Key
Unique Key
Foreign Key

Primary Key

A primary key is a column or a group of columns that represent each row in the table in an unique manner. As per Wikipedia, “A primary key is a specific choice of a minimal set of attributes that uniquely identify a tuple (row) in a relation“.

Note:

A table can only have one primary key and must contain unique values in it.

A) Define a PRIMARY KEY Constraint using create table

In a create table statement, you can define primary key for a table.

Syntax:

CREATE TABLE <table_name>

(

Column_name1 datatype(),

Column_name2 datatype(),

PRIMARY KEY (Column_name1)

);

For Example: The following table definition has student_id which is PRIMARY KEY.

CREATE TABLE Student_details
(
student_id INT AUTO_INCREMENT,
student_name varchar(50),
student_address varchar(200) PRIMARY KEY(student_id)
);

B) Define PRIMARY KEY Constraint Using Alter Table

In MySQL, we can also add a primary key on the column of the existing table.

Syntax:

ALTER TABLE <table_name>

ADD PRIMARY KEY (column_name);

For Example: First of all we have to create a table with the specified fields.

Create a table:

CREATE TABLE friend(
friend_id int,
friend_name varchar(200),
date DATETIME
);

Insert the values into the table:

INSERT INTO friend VALUES(1,'Ram','2020-08-27');
INSERT INTO friend VALUES(2,'Shayam','2020-08-27');

Result:

SELECT * from friend;

Command to view data type of Table named employees

The describe employees return the table attribute like fieldname,Type,Null,Key,Default,Extra etc.

Syntax: DESCRIBE friend;

Output

Command to add primary key into the Table name friend

The Alter Query is used to redefine the table 'friend' and add keyword is used to add primary key on column 'friend_id'.

Query:

ALTER TABLE friend
ADD PRIMARY KEY (friend_id);
Again type, DESCRIBE friend;

OUTPUT:

UNIQUE KEY

The alter unique key is to be used to changed the structure of the table and to add a unique key of the specified column in the table. The unique key allows one of the null values to be inserted in the column and the unique key table can contain more than one unique key. It is a column constraint.

A) Define a UNIQUE KEY Constraint using create table

1) When you want to define unique key for only one column

Syntax:

CREATE TABLE <table_name>

(

Column_name1 datatype() UNIQUE,

Column_name2 datatype(),

);

2) When you want to define more than one unique key on a table

Syntax:

CREATE TABLE <table_name>

(

Column_name1 datatype(),

Column_name2 datatype(),…

Column_namen datatype(),

UNIQUE (column_name1, column_name2)

);

For Example: Now, we are going to create a table with some unique key constraints.

CREATE TABLE VATSA(
ID INT AUTO_INCREMENT PRIMARY KEY,
Company_name varchar(100) UNIQUE,
Address varchar(250) UNIQUE
);

Now, insert some rows into it.

INSERT INTO VATSA VALUES (1, 'Vatsa_Empire', '132, ABC, Near Royal Club, BSR');
INSERT INTO VATSA VALUES (2, 'Vatsa_Hotel', '138, ABC, Near Royal Club, BSR');

Result:

SELECT * FROM Vatsa;

Now, I try to insert a new record with existing data, and then it will show an error.

INSERT INTO VATSA VALUES (10, 'Vatsa_Empire', '204, ABC, Near Royal Club, BSR');

You can also check this by typing the “SHOW CREATE TABLE Vatsa”.

B) Define a UNIQUE KEY Constraint using Alter table

In MySQL, we can also add a primary key on the column of the existing table.

Syntax:

ALTER TABLE <table_name>

ADD UNIQUE (column_name);

For example: If I want to apply unique key constraint on friend table, then use

ALTER TABLE friend
ADD CONSTRAINT uq_col
UNIQUE (friend_name);

Check: DESCRIBE friend

FOREIGN KEY

A Foreign key relationship between two database tables. These tables follow three conditions which are given below.

Both tables must be of the same database table types.
These fields which have to used in the foregin key relationship must be indexed.
The foregin key field relationship must be similiar in datatype.

Now, create a table

CREATE TABLE species (
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY(id)) ;

Insert some records into it.

INSERT INTO species VALUES
(1, 'birds'),
(2, 'dog'),
(3, 'cat'),
(4, 'monkey');

And, create another table whose name is zoo.

CREATE table zoo (
id int(4) NOT NULL,
name VARCHAR NOT NULL,
FK_species int NOT NULL,
INDEX (FK_species),
FOREIGN KEY (FK_species) REFERENCES species (id),
PRIMARY KEY(id)
);

As in the above command, relationship in a foreign key now exists between the fields zoo.species and species.id. An entry in the zoo table will be permitted only if the corresponding zoo.species field matches a value in the species id field. What happens when we have attempted to enter a record for Harry cat with an invalid species command:

Query

INSERT INTO zoo VALUES (1, 'Harry', 5);

OUTPUT

MySQL defines the species table to find if the species query exists and, finding that it does not, rejects the record. Contrast this with what happens when you enter the same record with a valid species code (one that already exists in the species table):

Query

INSERT INTO zoo VALUES (1, 'Harry', 3);

Here, MySQL findout the species table to see if the species query exists and, finding that it does, permits the record to be saved to the zoo table.

DELETE A FOREIGN KEY CONSTRAINT

To delete a foreign key relationship, first use the SHOW CREATE TABLE command to find out InnoDB's internal label for the field.

QUERY

SHOW create TABLE zoo

And then use the command of the ALTER TABLE with the DROP FOREIGN KEY given as below:

Query

ALTER TABLE zoo
DROP FOREIGN KEY zoo_ibfk_1;

ADD A FOREIGN KEY CONSTRAINT

To add a foreign key to an existing table, use the ALTER TABLE command with an ADD FOREIGN KEY clause to define the appropriate field as a foreign key:

Query

ALTER TABLE zoo
ADD FOREIGN KEY
(FK_species) REFERENCES species (id);

CONCLUSION

In this article, I have discussed the concept of keys in MySQL with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.

Thanks for reading this article!

Working with Keys in MySQL

Reactions

2 Comments

INTRODUCTION

Types of Keys

Primary Key

A) Define a PRIMARY KEY Constraint using create table

B) Define PRIMARY KEY Constraint Using Alter Table

Command to view data type of Table named employees

Command to add primary key into the Table name friend

UNIQUE KEY

A) Define a UNIQUE KEY Constraint using create table

1) When you want to define unique key for only one column

2) When you want to define more than one unique key on a table

B) Define a UNIQUE KEY Constraint using Alter table

FOREIGN KEY

DELETE A FOREIGN KEY CONSTRAINT

ADD A FOREIGN KEY CONSTRAINT

CONCLUSION