MySQL Key Constraints - C# Corner

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)   ); 

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

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

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

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

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

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 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   );

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

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!