Introduction

The constraints help to restrict what values should be stored in a table. The constraints provide limitations of the columns or data. This function helps to insert data in the table with a particular condition. It provides data security and table maintenance. If constraint does not support inserted data, then the table shows an error. You use either single constraint or multiple constraints in the single column or table.

Classification of the MySQL constraints

MySQL constraints have two types to restrict data. This classification is based on the use of the constraint inside of the table.

Table level constraints: the constraints apply to the whole table. This constraint provides restrictions on the entire table data.

Column level constraints: the constraints apply to a single column. This constraint provides restriction on column data.

Syntax

The table-level constraints syntax shows below. You need to apply once in the query.

CREATE TABLE table_name(
Column_names1 datatype,
Column_names2 datatype,
Constraints of table
);

The column level constraints syntax shows below.

CREATE TABLE table_name(
Column_names1 datatype Constraint,
Column_names2 datatype Constraint
);

The multiple constraints syntax shows below.

CREATE TABLE table_name(
Column_names1 datatype Constraint Constraint Constraint,
Column_names2 datatype Constraint Constraint,
Column_name datatype Constraint
);

MySQL constraint used in the table

The following constraints help to access data and apply restrictions on the table structure.

Constraints	Description
NOT NULL constraint	This constraint provides data in a column or table. The "NOT NULL" column does not remain empty.
AUTO_INCREMENT constraint	This constraint increments numerical data by one. Mainly, this function works with the index column. The "AUTO_INCREMENT" does not allow to remain a column empty.
UNIQUE constraint	This constraint provides unique data in a column or table.
PRIMARY KEY constraint	This constraint provides a base key of the index column. It helps to interconnect with another table.
FOREIGN KEY constraint	This constraint provides a secondary key of the index column. It helps to link with another table.
DEFAULT constraint	This constraint provides the default value for a column. You can access the value instead of the null value.
INDEX constraint	This constraint provides an index for referring to the table data. Mainly "index" constraint assigns for the left-most column of the table.
ENUM constraint	The constraint provides a list of constant values for a table or column. It accesses string or numerical values.
CHECK constraint	This constraint helps to check the available value of the table. The "CHECK" must check either columns or table data.

Prerequisites

MySQL table query helps to know about constraints. The CREATE table and insert data query into a table is necessary to use constraint. You know about data type and its information in table structure. You know about the MySQL system and its data structure in the fundamental table.

Let us learn each constraint in detail.

NOT NULL constraint

The NOT NULL constraint provides data in a column or table. The "NOT NULL" does not remain an empty column. The NOT NULL does not consider as a table-level constraint. You can apply it for columns as per requirement.

Syntax

The column level constraints syntax shows below. The query applies the "NOT NULL" constraint on each column.

CREATE TABLE table_name(
Column_names1 datatype NOT NULL,
Column_names2 datatype NOT NULL,
);

This syntax is used to update the table with NOT NULL constraints.

ALTER TABLE table_name MODIFY column datatype NOT NULL;

Examples of NOT NULL constraint

We will see the insert, update, and delete NOT NULL constraint example and its output below.

1) Example: create a table with the "NOT NULL" constraint.

Execute the below query to get a required table and its data. Apply constraint on table columns.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL,  
student_name VARCHAR(45) NOT NULL,
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45));

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above image shows table columns and their information. The first two fields apply the "not null" constraint. The other fields do not apply this constraint.

2) Example: Alter Table with "NOT NULL" constraint.

Execute the below query to alter the table and its data. Apply constraint on the table columns.

mysql> ALTER TABLE mysql 
MODIFY teacher_name VARCHAR (45) NOT NULL;

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above image shows table columns and their data. The first two fields apply the "not null" constraint. The other fields do not apply the "NOT NULL" constraint. The teacher name column updates from "null" to "not null" data.

3) Example: Alter Table to remove the "NOT NULL" constraint.

Execute the below query to get a required table and its data. Modify constraints on the table columns.

mysql> ALTER TABLE mysql 
MODIFY teacher_name VARCHAR(45) NULL;

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above image shows table columns and their data. The table fields apply the "not null" constraint. The other fields do not apply the "NOT NULL" constraint. The teacher name updates from "not null" to "null" data.

PRIMARY KEY constraint

The PRIMARY KEY constraint provides a base key of the index column. It helps to interconnect with another table. The primary key contains unique data in the table. The "PRIMARY KEY" does not allow to remain an empty column.

The table assigns a single primary key. The "primary key" contains either single or multiple columns of the table. This constraint works as a column level or table level. The primary key requires a "NOT NULL" constraint.

Syntax

The below syntax of the "PRIMARY KEY" works with the column level constraint.

CREATE TABLE table_name(
Column_names1 datatype PRIMARY KEY AUTO_INCREMENT,
);

The below syntax of the "PRIMARY KEY" works with the table level constraint.

CREATE TABLE table_name(
Column_names1 datatype,
Column_names2 datatype,
 PRIMARY KEY (Column_names1)
);

The syntax of the primary key with various columns shows below.

PRIMARY KEY (Column_names1, Column_names2)

Examples of the "PRIMARY KEY" constraint

We will see the insert, update, and delete PRIMARY KEY constraint example and its output below.

1) Example: The basic "PRIMARY KEY" constraint example shows below.

Execute the below query to assign the primary key for a column. This constraint assigns to a single column.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL PRIMARY KEY,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45));

OUTPUT

Execute the below query to get table information.

mysql> describe mysql;

The above output images show the primary key of the student_id. The "key" column shows the primary key in the table. The "student_id" assigns "PRI" as a primary key.

2) Example:

Execute the below query to assign the primary key for the table. This constraint assigns to a single column.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45) 
PRIMARY KEY  (student_id));

OUTPUT

Execute the below query to get table information.

mysql> describe mysql;

The above output images show the primary key of the student_id. The "key" column shows it in the table. The "student_id" assigns "PRI" as a primary key.

3) Example: The "PRIMARY KEY" for multiple columns example shows below.

Execute the below query to assign the primary key for the table. This constraint assigns to multiple columns.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL,  
student_name VARCHAR(45) NOT NULL,
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45), 
PRIMARY KEY (student_id, student_name));

OUTPUT

Execute the below query to get table information.

mysql> describe mysql;

The above output images show the primary key column. The "student_id" and "student_name" field assigns "PRI" as a primary key.

4) Example: Alter Table for "PRIMARY KEY" constraint example shows below.

Execute the below query to assign a primary key for a column or table. This constraint assigns the primary key to the required column.

mysql> ALTER TABLE mysql 
ADD primary key(student_id);

OUTPUT

Execute the below query to get the table constraint and its columns.

mysql> describe mysql;

The above output images show the primary key of the student_id. The "key" column shows it in the table. The "student_id" assigns "PRI" as a primary key.

5) Example: Alter Table for "PRIMARY KEY" constraint example shows below.

Execute the below query to assign a primary key for a column or table. This constraint assigns the primary key to the required column.

mysql> ALTER TABLE mysql 
DROP primary key;

OUTPUT

Execute the below query to get the table constraint and its columns.

mysql> describe mysql;

The above output image shows the primary key column. The primary key does not apply to the table.

AUTO_INCREMENT constraint

MySQL "AUTO_INCREMENT" constraints increment numerical data by one. Mainly this function works with the index column. The "AUTO_INCREMENT" does not remain in an empty column. It helps to handle information on the table. The "AUTO_INCREMENT" constraint applies with the "PRIMARY KEY" constraint.

Syntax

The query applies the "AUTO_INCREMENT" constraint on each column.

CREATE TABLE table_name(
Column_names1 datatype PRIMARY KEY AUTO_INCREMENT,
Column_names2 datatype 
);

This syntax is used to update the table with the AUTO_INCREMENT constraint.

ALTER TABLE table_name MODIFY column datatype PRIMARY KEY AUTO_INCREMENT;

Examples of AUTO_INCREMENT constraint

We will see the insert and update of an auto-increment constraint example and its output below.

1) Example: create a table with the "AUTO_INCREMENT" constraint.

Execute the below query to get a required table and its data. Apply constraint on numerical data columns.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT NOT NULL,  
teacher_name VARCHAR(45));

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above image shows table columns and their information. The "Extra" column shows "auto_increment" constraint. This constraint applies to the student_id field.

Execute below query to know about working procedure of the auto increment constraint.

mysql> insert mysql (student_name, marks, teacher_id, teacher_name) 
values ("rose", "68.4", 1, "adam"), ( "sam", "88.4", 3, "Ram");

Execute the following query that shows student_id is incremented automatically.

mysql> select * from mysql;

The "student_id" column inserts numerical values automatically. It is increment by one number. The table does not need to insert values manually.

2) Example: Alter Table with "AUTO_INCREMENT" constraint.

Execute the below query to get a required table and its data. Apply constraint on the numerical data columns.

mysql> ALTER TABLE mysql 
MODIFY teacher_id AUTO_INCREMENT;

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above image shows table columns and their information. This constraint applies to the student_id and teacher_id fields. The "Extra" column shows "auto_increment" constraint.

UNIQUE constraint

The UNIQUE constraint provides unique data in a column or table. The "UNIQUE" constraint includes different and unique data in the table. The "UNIQUE" constraint works with the "NOT NULL" constraint.

The primary key and unique constraint work similarly in the table. You do not apply the primary key for every column. The unique constraint works on every column of the table.

Syntax

The below syntax of the "UNIQUE" works with the column level constraint.

CREATE TABLE table_name(
Column_names1 datatype UNIQUE AUTO_INCREMENT,
Column_names2  datatype
);

The below syntax of the "UNIQUE" shows with the table level constraint.

CREATE TABLE table_name(
Column_names1 datatype,
Column_names2 datatype,
 UNIQUE (Column_names1)
);

The syntax of the UNIQUE constraint with various columns shows below.

UNIQUE (Column_names1, Column_names2)

Examples of the "UNIQUE" constraint

We will see the insert, update, and delete unique constraint example and its output below.

1) Example: The basic "UNIQUE" constraint example shows below.

Execute the below query to assign the UNIQUE for a column. This constraint assigns to a single column.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL UNIQUE,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45) );

OUTPUT

Execute below query to get table information with unique constraint.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "rose", "68.4", 1, "adam"), (1, "rose", "68.4", 1, "adam");

The above output image displays the error to insert a similar value.

2) Example: The "UNIQUE" constraint example shows below.

Execute the below query to assign the UNIQUE for the table. This constraint assigns to a single column.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT,  
teacher_name VARCHAR(45), 
UNIQUE  (student_id));

OUTPUT

Execute below query to get table information with unique constraint.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "rose", "68.4", 1, "adam"), (1, "sam", "88.4", 3, "Ram");

The above output image displays an error while inserting a similar student id. The unique key assigns to the index column.

3) Example: The "UNIQUE" for multiple columns example shows below.

Execute the below query to assign the UNIQUE data for the table. This constraint assigns to multiple columns.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL,  
student_name VARCHAR(45) NOT NULL,
marks FLOAT,	
teacher_id INT,  
teacher_name VARCHAR(45), 
UNIQUE  (student_id, student_name));

OUTPUT

Execute below query to get table information with unique constraint.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "rose", "68.4", 1, "adam"), (1, "rose", "88.4", 3, "Ram");

The unique key assigns to the first two columns. The above output image displays the error to insert a similar student id and student name.

4) Example: Alter Table for "UNIQUE" constraint example shows below.

Execute the below query to assign a UNIQUE for a column or table. This constraint assigns the UNIQUE value to the required column.

mysql> ALTER TABLE mysql 
ADD UNIQUE (student_id);

OUTPUT

Execute below query to get table information with unique constraint.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "rose", "68.4", 1, "adam"), (1, "sam", "88.4", 3, "Ram");

The above output image displays the error while inserting a similar student id. The unique key assigns to the index column. The table updates any column as unique. The student id must be of unique value.

5) Example: Alter Table for removing the "UNIQUE" constraint example shows below.

Execute the below query to assign a UNIQUE for a column or table. This constraint assigns the UNIQUE value to the required column.

mysql> alter table mysql drop index student_id;

OUTPUT

Execute below query to get table information with unique constraint.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "rose", "68.4", 1, "adam"), (1, "rose", "68.4", 1, "adam");

The above output image shows without a unique constraint table. It allows to stores similar data in the table.

CHECK constraint

The "CHECK" constraint is used to check the available value of the table. It must check columns or table data both. The check constraint uses conditions such as "equal to," "greater than," and so on. This constraint restricts the table values. It is a table-level constraint to apply limitations to the table data.

Syntax

The "CHECK" constraint syntax shows below.

CREATE TABLE table_name(
Column_names1 datatype NOT NULL,
Column_names2 datatype NOT NULL,
CHECK (Column_names condition )
);

The "CHECK" constraint alteration syntax shows below.

ALTER TABLE table_name ADD CHECK (Column condition);

The remove "CHECK" constraint syntax shows below.

ALTER TABLE table_name DROP CHECK column_name;

Examples of the "CHECK" constraint

We will see insert, update, and delete of the check constraint example and its output below.

1) Example: The basic "CHECK" constraint example shows below.

Execute the below query to get a required table and its data. Apply constraint on numerical data columns. Create a table with mark conditions.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT NOT NULL,  
teacher_name VARCHAR(45),
CHECK(marks > 40.2));

Execute the below query to insert table information and checkmarks condition.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name)
    -> values (1, "rose", "68.4", 1, "adam"), (2, "sam", "88.4", 3, "Ram");

OUTPUT

Execute the below query to get a description of the table.

mysql> select * from mysql;

You will see above output image. The marks are greater than "40.2" numbers. Hence, condition is fullfil.

Execute below query to see how to check condition become fail.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name)
     values (3, "radha", "85.4", 1, "merry"), (4, "juli", "38.4", 1, "Ram");

OUTPUT

The above output image shows an error. Index number 4 does not fulfill the check condition. The check constraint displays the error message.

2) Example: ADD "CHECK" constraint example shows below.

Execute the below query to insert check constraint in the teacher_id column. The teacher_id must be 1.

mysql> Alter table mysql add check (teacher_id < 4);

Execute below query to see how to check condition become fail.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name)
     values (3, "radha", "85.4", 6, "merry"), (4, "juli", "58.4", 5, "Ram");

OUTPUT

The above output image shows an error. The teacher id does not fulfill the check condition. The check constraint displays the error message.

DEFAULT constraint

This constraint provides the default value for column data. The default constraint set value and apply to the complete table. This constraint also sets a null value.

If you do not assign value to a column, then the default constraint sets its value. The "DEFAULT" constraint categorizes as a column-level constraint.

Syntax

The assign "DEFAULT" constraint syntax shows below.

CREATE TABLE table_name(
Column_names1 datatype DEFAULT Value,
Column_names2 datatype,
Column_names3 datatype DEFAULT Value
);

The "DEFAULT" constraint ALTER syntax shows below. The "SET" keyword uses to update constraints.

ALTER TABLE table_name
ALTER column_name SET DEFAULT Value;

The "DEFAULT" constraint remove syntax shows below. The "SET" keyword uses to remove constraints.

ALTER TABLE table_name(
ALTER column_name DROP DEFAULT;

Examples of the "DEFAULT" constraint

We will see the insert, update, and delete default constraint example and its output below.

1) Example: The basic "DEFAULT" constraint example shows below.

Execute the below query to get a required table and its data.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT NOT NULL,  
teacher_name VARCHAR(45) DEFAULT "rose");

OUTPUT

Execute the below query to get a description of the table.

mysql> describe mysql;

The above output image shows the last column is the default. The teacher name assign "rose" as a default value.

Execute the below query to insert table data. The table data adds with the teacher's name.

mysql> insert mysql ( student_id, student_name, marks, teacher_id, teacher_name)
         -> values (1, "sam", "85.4", 1, "merry");

OUTPUT

Execute the below query to get table information.

mysql> select * from mysql;

You see the above image with the given teacher's name. Here, the default teacher name does not apply to the table.

Execute the below query to insert table data. The data adds without the teacher's name.

mysql> insert mysql ( student_id, student_name, marks, teacher_id)
         -> values (2, "jerry", "75.3", 1);

OUTPUT

Execute below query to get table information.

mysql> select * from mysql;

You see the above image with the default teacher name. Here, the default teacher name applies to the table. You do not need to insert the teacher's name in the insert query.

2) Example: ADD " DEFAULT " constraint example shows below.

Execute the below query to update the default constraint. You insert the default constraint for other columns.

mysql> ALTER TABLE mysql
         -> ALTER teacher_id SET DEFAULT 1;

OUTPUT

Execute the below query to get table information. You know the default column in the table.

mysql> describe mysql;

The above output image shows the default last two columns. The teacher id and teacher name set the default value.

3) Example: Delete the "DEFAULT" constraint example shows below.

Execute the below query to update the default constraint. You can also remove default constraints from other columns.

mysql> ALTER TABLE mysql
         -> ALTER teacher_id DROP DEFAULT;

OUTPUT

Execute the below query to get table information. You know the default column in the table.

mysql> describe mysql;

The above output image shows the last column is the default. The teacher name assign "rose" as a default value. The teacher id column removes the default constraint.

FOREIGN KEY constraint

The "FOREIGN KEY" constraint provides a secondary key of the index column. It helps to link one table with another table. The foreign key always works with a primary key. Mainly both keys apply to the index column.

The foreign key link two tables using the primary key. The "FOREIGN KEY" constraint comes with the "REFERANCE" keyword. This keyword refers to the table's column to connect with another table.

Syntax

The below syntax of the "FOREIGN KEY" works with the table level constraint.

Create the first table with a primary key.

CREATE TABLE table_name1(
ColumnT1 datatype NOT NULL PRIMARY KEY,
ColumnT2 datatype NOT NULL);

Create a second table with a foreign key and primary key.

CREATE TABLE table_name2(
ColumnT1 datatype NOT NULL PRIMARY KEY,
ColumnT2 datatype NOT NULL,
Column1 datatype NOT NULL,
Foreign key (Column1) REFERENCE table_name1 (Column1) 
);

You can modify, add, and delete foreign keys using the "alter table" query. Alter "foreign key" constraint syntax shows below. The foreign key requires a column with a primary key. The column must apply the "not null" constraint. The column does not have without value.

ALTER TABLE table_name2
ADD Foreign key (Column1) REFERENCE table_name1 (Column1);

Examples of the "FOREIGN KEY" constraint

We will see the insert, update, and delete FOREIGN KEY constraint example and its output below.

1) Example: The basic "FOREIGN KEY" constraint example shows below.

Create the first table to store student information. The student_id connects as a foreign key. This column assigns the primary key and not null constraints.

mysql> Create table mysql_student(
student_id INT NOT NULL PRIMARY KEY,  
student_name VARCHAR(45),
marks FLOAT);

OUTPUT

Execute the below query to get the mysql_student table.

mysql> select * from mysql_student;

The above output image shows a table with a primary key. This table is essential as a reference table for the foreign key.

Create a second table to store teacher information. The foreign key assigns the student_id column for connection. The "REFERENCE" keyword assigns the mysql_student table with the respective field.

mysql> Create table mysql_teacher(
teacher_id INT NOT NULL PRIMARY KEY,  
student_id INT,
teacher_name VARCHAR(45),
review VARCHAR(45),
FOREIGN KEY (student_id) REFERENCES mysql_student(student_id));

OUTPUT

Execute the below query to get the mysql_teacher table.

mysql> select * from mysql_teacher;

The mysql_teacher table link with the mysql_student table. The foreign key applies to the student_id column. The teacher_id shows the primary key. The student_id column shows the "MUL" key.

2) Example: The store value using the "FOREIGN KEY" constraint example shows below.

Execute the below query to insert information in the table. The student_id value must be unique.

mysql> INSERT INTO `tutorial`.`mysql_student` (`student_id`, `student_name`, `marks`) 
VALUES ('1', 'radha', '78.4'), ('2', 'adnan', '87.3'), ('3', 'johny', '56'), ('4', 'merry', '67.4');

Output

You see the mysql_student table information. It is a basic table that connects with the mysql_teacher table.

Execute the below query to get student information from the table.

mysql> select * from mysql_student;

Execute following query to insert data. The value stores in the mysql_teacher table. The manually assign student_id value to connect with mysql_student table.

mysql> INSERT INTO mysql_teacher (teacher_id,  student_id, teacher_name, review) 
values (1, 3,  'dhara', 'good'), (2, 1,  'dhara', ' excellent'), (3, 2,  'alen', ' very good'), (4, 1,  'sara', 'good');

Output

Execute the below query to get student information from the table.

mysql> select * from mysql_teacher;

You see the mysql_teacher table information. This table connects with the mysql_student table. Now, the mysql_student table and "mysql_teacher" table connect with each other using the "student_id" column.

3) Example: The deletes example of the "FOREIGN KEY" constraint example shows below.

Execute the following query to delete the mysql_student table.

mysql> drop table mysql_student;

The above image shows an error. The table does not remove from a database because of a foreign key. The reference table provides conditions about the update and deletes data of the table.

Execute the following query to delete the mysql_teacher table.

mysql> drop table mysql_teacher;

The second table deletes quickly. It does not affect the reference table. The reference column also deletes permanently. Now you delete the first table easily.

Now, the first table does not connect with another table. Execute the following query to delete the mysql_student table.

mysql> drop table mysql_student;

The "mysql_student" table removes permanently from the database.

INDEX constraint

This constraint provides an index for referring to the table data. The "index" constraint refers to the id of the row. You can access, search, read, and delete table data using an index. The index does not show on table information.

The "index" constraint assigns any column of the table. Primarily "index" constraint assigns for the left-most column of the table. This constraint can apply to more than one column in the table.

Syntax

The primary index constraint syntax shows below. The index uses to store duplicate values in the table.

CREATE INDEX index_nm ON table name (column_name1, ….., column_nameN);

The unique index constraint syntax shows below. The index uses to store duplicate values in the table.

CREATE UNIQUE INDEX index_nm ON table name (column_name1, ….., column_nameN);

The index constraint deletes syntax shows below. The drop index uses to remove constraints in the table.

ALTER TABLE table-name DROP INDEX index name;

Examples of the "index" constraint

We will see the insert, update, and delete index constraint example and its output below.

Create a table to apply index constraints to the table.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT NOT NULL,  
teacher_name VARCHAR(45));

1) Example: The basic "index" constraint example shows below.

Execute the below query to apply a constraint on a column of the table.

mysql> CREATE INDEX ind_name
ON mysql ( student_name, teacher_name);

OUTPUT

Execute the below query to get output.

mysql> select * from mysql use index(ind_name);

The index constraint does not show as an output. But you see the speed of the query execution.

2) Example: The unique "index" constraint example shows below.

Execute the below query to apply a constraint on a column of the table.

mysql> CREATE UNIQUE INDEX ind_id
ON mysql ( student_id, teacher_id);

OUTPUT

Execute the below query to get output.

mysql> select * from mysql use index(ind_id);

The index constraint does not show as an output. But you see the speed of the query execution.

4) Example: Delete the "index" constraint example shows below.

Execute the below query to apply a constraint on a column of the table.

mysql> ALTER TABLE mysql DROP INDEX ind_name;

OUTPUT

You will see the above image after remove the index constraint.

mysql> select * from mysql;

The index constraint does not show as an output. But you see the speed of the query execution.

ENUM constraint

The ENUM constraint provides a list of constant values for a table or column. It accesses string or numerical values. The ENUM is a data type with its value. The value sets either string or numerical.

Syntax

The following syntax shows ENUM and its value. Here ENUM sets some constant values for a column.

CREATE TABLE table name (    
    Column1 data type PRIMARY KEY,     
    Column2 data type,
    Column3 ENUM ('value1', ' value2', ' value …', ' valueN')    
);

Examples of the "ENUM" constraint

You will see the ENUM constraint example and its output below.

1) Example: The basic "ENUM" constraint example shows below.

Execute below query to create table with ENUM data type or constraint.

mysql> CREATE TABLE mysql ( 
student_id INT NOT NULL UNIQUE,  
student_name VARCHAR(45),
marks FLOAT,
teacher_id INT,  
teacher_name ENUM('rose', 'alen', 'ram', 'merry') 
);

OUTPUT

Execute the following query to know the ENUM value and its column.

mysql> describe mysql;

You will see the output image with the table field, several data types, and ENUM. The teacher name applies ENUM with constant values.

2) Example: Add the value of the ENUM constraint example shows below.

Execute insert query to stores values with ENUM data.

mysql> insert mysql (student_id, student_name, marks, teacher_id, teacher_name) 
values (1, "adam", "68.4", 1, "rose"), ( 2, "sam", "88.4", 3, "Ram"), (3, "rose", "67.2", 2, "alen");

OUTPUT

Execute the below query for table information.

mysql> select * from mysql;

The above image display teacher's name with ENUM values. Teacher name column stores only enum values.

3) Example: Add Non ENUM value example shows below.

Execute insert query to store non enum value in the table. The teacher name does not assign enum value.

mysql> insert mysql (student_id, student_name, marks, teacher_id, teacher_name) 
values (4, "adnan", "68.4", 5, "sandy");

OUTPUT

If the enum value does not store in the table, then the output shows an error. It would help if you used enum values in the table.

MySQL Tutorial

MySQL Join

MySQL Function

Questions

Misc

MySQL Tutorial

MySQL Join

MySQL Function

Questions

Misc

MySQL Constraints