Post
Ask Question

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.
  1. Super Key
  2. Candidate Key
  3. Primary Key
  4. Unique Key
  5. 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.
  1. CREATE TABLE Student_details  
  2. (  
  3.      student_id INT AUTO_INCREMENT,  
  4.      student_name varchar(50),  
  5.      student_address varchar(200) PRIMARY KEY(student_id)  
  6. ); 

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:
  1. CREATE TABLE friend(  
  2. friend_id int,  
  3. friend_name varchar(200),  
  4. date DATETIME  
  5. ); 
 
Insert the values into the table:
  1. INSERT INTO friend VALUES(1,'Ram','2020-08-27');  
  2. INSERT INTO friend VALUES(2,'Shayam','2020-08-27'); 
Result:
  1. 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:
  1. ALTER TABLE friend  
  2. ADD PRIMARY KEY (friend_id);  
  3. 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.
  1. CREATE TABLE VATSA(  
  2.      ID INT AUTO_INCREMENT PRIMARY KEY,  
  3.      Company_name varchar(100) UNIQUE,  
  4.      Address varchar(250) UNIQUE  
  5. ); 
 
Now, insert some rows into it.
  1. INSERT INTO VATSA VALUES (1, 'Vatsa_Empire', '132, ABC, Near Royal Club, BSR');  
  2. INSERT INTO VATSA VALUES (2, 'Vatsa_Hotel', '138, ABC, Near Royal Club, BSR'); 
Result:
  1. SELECT * FROM Vatsa; 
 
Now, I try to insert a new record with existing data, and then it will show an error.
  1. 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
  1. ALTER TABLE friend  
  2. ADD CONSTRAINT uq_col  
  3. 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.
  1. Both tables must be of the same database table types.
  2. These fields which have to used in the foregin key relationship must be indexed.
  3. The foregin key field relationship must be similiar in datatype.
Now, create a table
  1. CREATE TABLE species (  
  2. id int NOT NULL AUTO_INCREMENT,  
  3. name VARCHAR(50) NOT NULL,  
  4. PRIMARY KEY(id)) ; 
Insert some records into it.
  1. INSERT INTO species VALUES  
  2. (1, 'birds'),  
  3. (2, 'dog'),  
  4. (3, 'cat'),  
  5. (4, 'monkey'); 
And, create another table whose name is zoo.
  1. CREATE table zoo (  
  2. id int(4) NOT NULL,  
  3. name VARCHAR NOT NULL,  
  4. FK_species int NOT NULL,  
  5. INDEX (FK_species),  
  6. FOREIGN KEY (FK_species) REFERENCES species (id),  
  7. PRIMARY KEY(id)  
  8. ); 
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
  1. INSERT INTO zoo VALUES (1, 'Harry', 5); 
OUTPUT
 
mysql11
 
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
  1. 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
  1. SHOW create TABLE zoo 
 
And then use the command of the ALTER TABLE with the DROP FOREIGN KEY given as below:
 
Query
  1. ALTER TABLE zoo  
  2.  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
  1. ALTER TABLE zoo  
  2. ADD FOREIGN KEY  
  3. (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!


Similar Articles