Post
Ask Question

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 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 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.

CREATE TABLE < table_name > (  
  Primary_key_column Datatype PRIMARY KEY,  
); 

b. When the Primary key contains more than one column.

CREATE TABLE < table_name > (  
  Primary_key_column1 Datatype,  
  Primary_key_column2 Datatype,  
  PRIMARY KEY(Primary_key_column1, Primary_key_column2)  
);  

The following command creates a PRIMARY KEY on the "C_Id" column when the table ‘Company’ has been created.

Example

CREATE TABLE Company  
(  
  C_Id int NOT NULL,  
  CompanyName varchar(255) NOT NULL,  
  Establishment_date datetime NOT NULL,  
  Address varchar(255),  
  Description varchar(255),  
  PRIMARY KEY(C_Id)  
); 

primarykey

2. PRIMARY KEY Constraint Using ALTER TABLE

a)If the table is already created and we have to add a primary key to 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

primarykeyaltertable

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

queryALTER

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

dropprimarykey

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

CREATE TABLE Company  
(  
  C_Id int NOT NULL,  
  CompanyName varchar(255) NOT NULL,  
  Establishment_date datetime NOT NULL,  
  Address varchar(255) NOT NULL,  
  Description varchar(255),  
  UNIQUE(C_ID)  
); 

Example

createtable

2. UNIQUE KEY CONSTRAINT Using ALTER TABLE

a.If the table is already created and we have to add a unique key to 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

adduniqueinsql

b)To allow the 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

tablequery

3. DROP UNIQUE KEY Constraints

To drop a UNIQUE constraint, use the following Syntax

Syntax

ALTER TABLE <table_name>
DROP INDEX <constraint_name>;

Example

previewtable

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 the foreign key “constraint name” that you want.
  • Then, after the “FOREIGN KEY” keyword, specify the list of foreign key columns. Note that the foreign key name is optional, and if you don’t specify it, 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 MySQL when any value is updated or deleted in the parent table.

MySQL has 5 reference options

  • CASCADE
  • SET NULL
  • NO ACTION
  • RESTRICT
  • 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

CREATE TABLE Parent_Table(  
  P_ID INT AUTO_INCREMENT,  
  PRIMARY KEY(P_ID)  
);  
  CREATE TABLE Child_Table(  
  C_ID INT AUTO_INCREMENT PRIMARY KEY,  
  Parent_ID INT,  
  CONSTRAINT par_ind FOREIGN KEY(Parent_ID) REFERENCES Parent_Table(P_ID) ON DELETE CASCADE  
);

parentstable

resultgrid

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

query1

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

https://www.mysqltutorial.org/

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!


Similar Articles