MySQL Constraints

                         MySQL Constraints

The constraint in MySQL is used to specify the rule that allows or restricts what values/data will be stored in the table. They provide a suitable method to ensure data accuracy and integrity inside the table. It also helps to limit the type of data that will be inserted inside the table. If any interruption occurs between the constraint and data action, the action is failed.

Types of MySQL Constraints

Constraints in MySQL is classified into two types:

1. Column Level Constraints: These constraints are applied only to the single column that limits the type of particular column data.
2. Table Level Constraints: These constraints are applied to the entire table that limits the type of data for the whole table.

How to create constraints in MySQL

We can define the constraints during a table created by using the CREATE TABLE statement. MySQL also uses the ALTER TABLE statement to specify the constraints in the case of the existing table schema.

Syntax

The following are the syntax to create a constraints in table:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Constraints used in MySQL

The following are the most common constraints used in the MySQL:

• NOT NULL
• CHECK
• DEFAULT
• PRIMARY KEY
• AUTO_INCREMENT
• UNIQUE
• INDEX
• ENUM
• FOREIGN KEY

Let us discuss each of these constraints in detail.

In this blog let us discuss only 3 constraints and remaining in next blog

1. Not NULL CONSTRAINT
2. UNIQUE CONSTRAINT
3. CHECK CONSTRAINT

NOT NULL Constraint

This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraint.

mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

Execute the queries listed below to understand how it works:

mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');  
  
mysql> INSERT INTO Student VALUES(2, 'peter', 'Amanda', 'Florida');   

UNIQUE Constraint

This constraint ensures that all values inserted into the column will be unique. It means a column cannot stores duplicate values. MySQL allows us to use more than one column with UNIQUE constraint in a table. The below statement creates a table with a UNIQUE constraint:

mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));  

Execute the queries listed below to understand how it works:

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloons', 38), (2, 'Cantabil', 40);  
  
mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond', 38), (2, 'xxx', 40);   

CHECK Constraint

It controls the value in a particular column. It ensures that the inserted value in a column must be satisfied with the given condition. In other words, it determines whether the value associated with the column is valid or not with the given condition.

Syntax:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]  

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int CHECK (Age>=18)  
);  

mysql> INSERT INTO Persons(Id, Name, Age)   
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);  
  
mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);  

As first statements executes successfully but the second statement does not executes as it violates the syntax.