Updated May 11, 2023
Introduction to MySQL Constraints
MySQL constraints are statements that can be applied at the column level or table level to specify rules for the data that can be entered into a column or data table, i.e., constraints are basically limitations or restrictions on the data type. Hence, they ensure the data’s reliability, consistency, and accuracy. In SQL, we will come across the following types of constraints. Here is an overview of the generic purpose that these constraints serve.
| SQL Constraint | Function |
| NOT NULL | It ensures that a column does not accept NULL values. |
| CHECK | It ensures that a column accepts values within the specified range of values. |
| UNIQUE | It ensures that a column does not accept duplicate values. |
| PRIMARY KEY | It uniquely identifies a row in the table. It is a combination of NOT NULL and UNIQUE constraints. |
| FOREIGN KEY | It is like a primary key constraint only. But it uniquely identifies a row in another table. |
| DEFAULT | It ensures that the column sets a default value for empty records. |
Types of Constraints in MySQL with Examples
Following are the types and examples of MySQL Constraints are given below:
1. NOT NULL CONSTRAINT
When a NOT NULL constraint is applied to a column, it ensures it will not accept NULL values. The syntax for the NOT NULL constraint in MYSQL is as follows :
Syntax:
CREATE TABLE table_name(
column_name_1 datatype NOT NULL,
column_name_2 datatype NOT NULL,
.
.
column_name_n datatype NOT NULL
);Parameters:
The parameters used in the syntax are :
- CREATE TABLE table_name: This statement is used to create a new database table. Specify the name of the table.
- column_name1, column_name2, …: Specify the name of the columns you want to create on the table.
- datatype: Specify the datatype of each column in the table.
- [NULL | NOT NULL]: Specify whether it can hold NULL values.
Example
SQL query to create NOT NULL constraint while creating a table.
Query:
CREATE TABLE students (
student_ID int NOT NULL,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
);Output:
We have created a student table with three columns accepting non-NULL values. Let’s check if these columns accept null values or not.
We will try inserting a student record in the table containing data for only three columns. The insert statement will fail because we did not supply any values for class_name.
INSERT INTO students (student_ID,student_Name,Age)
VALUES(13,'Holly',9);Output:
2. CHECK CONSTRAINT
When the CHECK constraint is applied to a column, it ensures it will not accept data values outside the specified range. The CHECK constraint can also be mentioned to ensure that data meets a specified condition like less than, equal to, not, etc.
Syntax:
CREATE TABLE table_name(
column_name_1 datatype,
column_name_2 datatype
CHECK(condition_on_column_name_2),
.
.
column_name_n datatype
);Parameters:
- CREATE TABLE table_name: This statement is used to create a new database table. Specify the name of the table.
- column_name1, column_name2, … : Specify the name of the columns you want to create on the table.
- datatype: Specify the datatype of each column in the table.
- CHECK(condition_on_column_name_2): Specify the condition data should meet before entering a column.
Example
SQL query to create CHECK constraint while creating a table.
Query:
CREATE TABLE students (
student_ID int NOT NULL,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
CHECK(Age >9)
);Output:
Here, we have created a student table with three columns that accept non-NULL values and the fourth column, which will age greater than 9 only. Let’s check if the columns meet the specified conditions. We will try to insert a student record in the table which contains an age less than 9. The insert statement will fail.
INSERT INTO students (student_ID,student_Name,class_name,Age)
VALUES(13,'Holly','IV',8);Output:
3. UNIQUE KEY CONSTRAINT
A unique key is a constraint in SQL that helps uniquely identify a record in the datatable. It can be considered somewhat similar to the Primary key as both guarantee a record’s uniqueness. But unlike the primary key, a unique key can accept NULL values and be used on more than one column of the datatable.
Syntax:
CREATE TABLE table_name
(
Column_name1 datatype [NULL | NOT NULL] UNIQUE,
Column_name2 datatype [NULL | NOT NULL],
Column_name3 datatype [NULL | NOT NULL]
);Parameters:
- CREATE TABLE: This statement is used to create a new database table.
- Column_name1, Column_name2, Column_name3: Mention the name of the columns you want to create on the table.
- datatype: Mention the datatype of each column in the table.
- [NULL | NOT NULL]: Mention whether it can hold NULL values.
- UNIQUE: Unique keyword written with the column name creates a unique key constraint. It ensures that there are no duplicate values in that particular column.
Example
SQL query to create unique constraints while creating a table.
Query:
CREATE TABLE students (
student_ID int UNIQUE,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
);Output:
4. PRIMARY KEY CONSTRAINT
A primary key constraint uniquely identifies a record or row in the data table. It does not accept NULL values and accepts unique values.
Syntax:
CREATE TABLE table_name(
column_name_1 datatype NOT NULL,
column_name_2 datatype NOT NULL,
.
.
column_name_n datatype NOT NULL
PRIMARY KEY (column_name_1)
);Parameters:
- CREATE TABLE table_name: This statement is used to create a new database table. Specify the name of the table.
- column_name1, column_name2, … : Specify the name of the columns you want to create on the table.
- datatype: Specify the datatype of each column in the table.
- PRIMARY KEY (column_name_1): Specify the column name on which the primary key constraint has to be applied. It should be a NOT NULL column.
Example
The SQL query creates primary key constraints while creating a table.
Query:
CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255),
Age int,
PRIMARY KEY (Student_ID)
);Output:
5. FOREIGN KEY CONSTRAINT
A foreign key constraint uniquely identifies a record or row in another data table. It refers to the primary key in the other table. So, it acts as a link between two tables.
Syntax:
CREATE TABLE table_name_1(
column_name_1 datatype NOT NULL,
column_name_2 datatype NOT NULL,
.
.
column_name_n datatype NOT NULL
PRIMARY KEY (column_name_1)
FOREIGN KEY (column_name_2) REFERENCES table_name_2(column_name_2)
);Parameters:
- CREATE TABLE table_name_1: This statement is used to create a new database table. Specify the name of the table.
- column_name1, column_name2, … : Specify the name of the columns you want to create on the table.
- datatype: Specify the datatype of each column in the table.
- PRIMARY KEY (column_name_1): Specify the column name on which the primary key constraint has to be applied. It should be a NOT NULL column.
- FOREIGN KEY (column_name_2): Specify the column name on which foreign key constraint has to be applied.
- REFERENCES table_name_2(column_name_2): Specify the table name and the column on which the foreign key in the first table is linked.
Example
SQL query to create foreign key constraints while creating a table.
Query:
CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255),
Age int,
PRIMARY KEY (Student_ID)
FOREIGN KEY (Class_Name) References classes(Class_Name)
);Output:
In the above example, we have created a foreign key constraint on Class_Name which references the classes table.
6. DEFAULT CONSTRAINT
To ensure consistency in data, we sometimes require missing data or non-entered values to be set to a default value. We can use the DEFAULT constraint in such cases to ensure that the column is set to default.
Syntax:
CREATE TABLE table_name(
column_name_1 datatype NOT NULL,
column_name_2 datatype DEFAULT 'default_value',
.
.
);Example
SQL query to create default constraints while creating a table.
Query:
CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255) DEFAULT 'IV'
);Output:
In the above example, the Class_Name column in the student’s table will default the class name to IV if nothing is specified.
We are inserting values for two columns only.
INSERT INTO Students ( [Student_ID], [Student_Name] ) VALUES (142,'Molly');Output:
We just inserted values for two columns, but we can see in the table that the class name has been set to an IV default value.
SELECT TOP 1000 [Student_ID], [Student_Name], [Class_Name] FROM [practice_art] . [dbo] . [Students]Output:
Recommended Articles
We hope that this EDUCBA information on “MySQL Constraints” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
