Home » Learn – MySQL Constraints types

Learn – MySQL Constraints types

  • by

Learn – 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 MySQLColumn Level Constraints:

These constraints are applied only to the single column that limits the type of particular column data.

Table Level Constraints: These constraints are applied to the entire table that limits the type of data for the whole table.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 new_table_name (
col_name1 datatype constraint,
col_name2 datatype constraint,
col_name3 datatype constraint,
.........
);

Constraints used in MySQL

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

  1. NOT NULL
  2. CHECK
  3. DEFAULT
  4. PRIMARY KEY
  5. AUTO_INCREMENT
  6. UNIQUE
  7. INDEX
  8. ENUM
  9. FOREIGN KEY

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, NULL, '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))
;

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

For example, the following statement creates a table “Persons” that contains CHECK constraint on the “Age” column. The CHECK constraint ensures that the inserted value in a column must be satisfied with the given condition means the Age of a person should be greater than or equal to 18

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

Execute the listed queries to insert the values into the table

:



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

 

DEFAULT Constraint- This constraint is used to set the default value for the particular column where we have not specified any value. It means the column must contain a value, including NULL.

For example, the following statement creates a table “Persons” that contains DEFAULT constraint on the “City” column. If we have not specified any value to the City column, it inserts the default value

mysql > CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(45) NOT NULL,
Age int,
City varchar(25) DEFAULT 'New York'
);

Execute the listed queries to insert the values into the table

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


mysql > INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);

We can see that it works perfectly. It means default value “New York” stored automatically in the City column.

PRIMARY KEY Constraint-This constraint is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains unique value into a column.The following statement creates a table “Person” and explains the use of this primary key more clearly:

CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
Name varchar(45) NOT NULL,
Age int,
City varchar(25));

AUTO_INCREMENT Constraint-This constraint automatically generates a unique number whenever we insert a new record into the table. Generally, we use this constraint for the primary key field in a table.We can understand it with the following example where the id column going to be auto-incremented in the Animal table

mysql > CREATE TABLE Animals(
id int NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id));

ENUM Constraint-The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.The following illustration creates a table named “shirts” that contains three columns:
id, name, and size. The column name “size” uses the ENUM data type that contains small, medium, large, and x-large sizes.

mysql > CREATE TABLE Shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM('small', 'medium', 'large', 'x-large')
);

INDEX Constraint-This constraint allows us to create and retrieve values from the table very quickly and easily. An index can be created using one or more than one column. It assigns a ROWID for each row in that way they were inserted into the table.The following illustration creates a table named “shirts” that contains three columns:
id, name, and size.


mysql >
CREATE TABLE Shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM('small', 'medium', 'large', 'x-large')
);


mysql > CREATE INDEX idx_name ON Shirts(name);

We can use the query below to retrieve the data using the index column

mysql > SELECT * FROM Shirts USE INDEX(idx_name);