MySQL Key Constraints

INTRODUCTION

 
In this tutorial, I am going to explain about Key Constraints in MySQL with examples.
 

KEY CONSTRAINT

 
A constraint allows columns in a table that restricts how many types of changeable data values are in a column of the table.
 

PRIMARY KEY Constraint

 
A Primary Key is defined as a column or a set of columns that uniquely identifies each row in the table.
 
In the primary key constraints, there are some different things such as the ones given below.
  • Each record uniquely identifies in a primary key constraint in a database table.
  • The primary keys must contain unique values.
  • NULL values are not allowed in primary key columns.
  • Each table should have a primary key, and can have only the one primary key.
  • A table can have only one primary key, but the primary key can consist of more than one column.

1) Define a PRIMARY KEY Constraint Using CREATE TABLE

 
Syntax
 

a) When a Primary key contains a single column

  1. CREATE TABLE < table_name > (  
  2.   Primary_key_column Datatype PRIMARY KEY,  
  3. ); 

b) When the Primary key contains more than one column

  1. CREATE TABLE < table_name > (  
  2.   Primary_key_column1 Datatype,  
  3.   Primary_key_column2 Datatype,  
  4.   PRIMARY KEY(Primary_key_column1, Primary_key_column2)  
  5. );  
The following command creates a PRIMARY KEY on the "C_Id" column when table ‘Company’ has been created.
 
Example
  1. CREATE TABLE Company  
  2. (  
  3.   C_Id int NOT NULL,  
  4.   CompanyName varchar(255) NOT NULL,  
  5.   Establishment_date datetime NOT NULL,  
  6.   Address varchar(255),  
  7.   Description varchar(255),  
  8.   PRIMARY KEY(C_Id)  
  9. ); 
 

2) PRIMARY KEY Constraint Using ALTER TABLE

 
a) If the table is already created and we have to add a primary key on it, then we use ALTER TABLE command to add a primary key constraint using alter table command.
 
Syntax
ALTER TABLE <table_name>
ADD PRIMARY KEY (column_name);
 
Example
 
 
b) A table can have only one primary key, but the primary key can consist of more than one column.
 
Syntax:
ALTER TABLE <table_name>
ADD CONSTRAINT < constraint_name> PRIMARY KEY (column_list);
 
Example
 
 

3) DROP PRIMARY KEY Constraint

 
When we need to drop a PRIMARY KEY constraint then, we use the following commands.
 
Syntax
ALTER TABLE Persons
DROP PRIMARY KEY;
 
Example
 
 

UNIQUE KEY CONSTRAINT

 
In the UNIQUE constraint, each record is uniquely identified in a database table.  
 
Automatically a PRIMARY KEY constraint has a UNIQUE constraint defined on it.
 

1) UNIQUE KEY CONSTRAINT Using CREATE TABLE Command

 
The following command creates a UNIQUE constraint on the "C_Id" column when the table “Company” is created.
 
Syntax
  1. CREATE TABLE Company  
  2. (  
  3.   C_Id int NOT NULL,  
  4.   CompanyName varchar(255) NOT NULL,  
  5.   Establishment_date datetime NOT NULL,  
  6.   Address varchar(255) NOT NULL,  
  7.   Description varchar(255),  
  8.   UNIQUE(C_ID)  
  9. ); 
Example
 
 

2) UNIQUE KEY CONSTRAINT Using ALTER TABLE

 
a) If the table is already created and we have to add a unique key on it, then we use the ALTER TABLE command to add a unique key constraints using alter table command.
 
Syntax
ALTER TABLE <table_name>
ADD UNIQUE (column_name);
 
Example
 
 
b) To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the below command.
 
Syntax
ALTER TABLE <table_name>
ADD CONSTRAINT < constraint_name> UNIQUE (column_list);
 
Example
 
 

3) DROP UNIQUE KEY Constraints

 
To drop a UNIQUE constraint use the following Syntax
 
Syntax
ALTER TABLE <table_name>
DROP INDEX <constraint_name>;
 
Example
 
 

FOREIGN KEY CONSTRAINT

 
The individual purpose of a foreign key constraint is to define a relationship between two tables.
 
Defining a FOREIGN KEY constraint
 
Now, I am showing you the basic syntax to define foreign key constraints in the CREATE Table command.
 
Syntax
 
CREATE TABLE <table_name> (
<column_lists>,
[CONSTRAINT constraint_name]
FOREIGN KEY (Foreign_key_name) (column_name)
REFERENCES <parent_table_name> (column_name)
[ON DELETE/UPDATE reference_option]
 
Here, in this syntax:
  • After the “CONSTRAINT” keyword, specify the name of foreign key “constraint name” that you want.
  • Then, after the “FOREIGN KEY” keyword, specify the list of foreign key columns. Note that, foreign key name is optional and if you don’t specify, it will generate automatically.
  • Now, specify the “parent_table_name” followed by a “column_name” to which the foreign key columns reference.
  • Finally, specify the “reference_option” which determines the actions that are taken by the MySQL when any value is updated or deleted in parent table.
MySQL has 5 reference options:
 
1. CASCADE
2. SET NULL
3. NO ACTION
4. RESTRICT
5. SET DEFAULT
 

1) Define a FOREIGN KEY Constraint Using CREATE TABLE

 
Here is a simple example that relates parent and child tables through a single-column using a foreign key concept. Let’s see.
 
Example
  1. CREATE TABLE Parent_Table(  
  2.   P_ID INT AUTO_INCREMENT,  
  3.   PRIMARY KEY(P_ID)  
  4. );  
  5.   
  6.   
  7. CREATE TABLE Child_Table(  
  8.   C_ID INT AUTO_INCREMENT PRIMARY KEY,  
  9.   Parent_ID INT,  
  10.   CONSTRAINT par_ind FOREIGN KEY(Parent_ID) REFERENCES Parent_Table(P_ID) ON DELETE CASCADE  
  11. ); 
 
 

2) FOREIGN KEY Constraint Using ALTER TABLE

 
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following commands.
 
Syntax
ALTER TABLE <child_table_name>
ADD FOREIGN KEY (column_name)
REFERENCES <Parent_table_name>(column_name);
 
Example
 
 

3) DROP FOREIGN KEY Constraints

 
When we have to drop a FOREIGN KEY constraint, use the following MySQL syntax.
 
Syntax
ALTER TABLE <table_name>
DROP FOREIGN KEY <constraint_name>;
 
Reference
 

CONCLUSION

 
In this article, I have discussed the concept of Key Constraints 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!