EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL Constraints

MySQL Constraints

Priya Pedamkar
Article byPriya Pedamkar

Updated May 11, 2023

MySQL Constraints

 

 

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL Constraints output 1

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:

MySQL Constraints output 2

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:

MySQL Constraints output 3

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:

MySQL Constraints output 4

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:

MySQL Constraints output 5

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:

output 6

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:

  1. CREATE TABLE table_name_1: This statement is used to create a new database table. Specify the name of the table.
  2. column_name1, column_name2, … : Specify the name of the columns you want to create on the table.
  3. datatype: Specify the datatype of each column in the table.
  4. 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.
  5. FOREIGN KEY (column_name_2): Specify the column name on which foreign key constraint has to be applied.
  6. 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:

output 7

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:

output 8

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:

output 9

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:

output 10

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.

  1. ANY in MySQL | How to Work?
  2. Guide to MySQL Subquery
  3. MySQL IN Operator | Examples
  4. MySQL Timestamp | How to Work?