What are Constraints in MySQL?
Constraints in MySQL is used to define rules for what values can be stored in rows and columns in a table. It is used to limit the data that can be inserted into a table. The action is canceled if there is a conflict between the constraint and the data action. This also helps in maintaining the reliability and accuracy of the data.
NOT NULL Constraint in MySQL
The NOT NULL constraint is used for a column in a table. It ensures that there should not be any NULL value in that column. It is generally assigned for a column while creating a table. The syntax for this is shown below.
column_name datatype NOT NULL;In the above syntax, it can be seen that NOT NULL is used after the column name and data type of that column in the column definition.
For example -
Let's create a table named students.
CREATE TABLE students(
reg_no INT NOT NULL,
name VARCHAR(255) NOT NULL,
admission_date DATE
);The above statements after execution create a table named students.
In the table students, there are three columns reg_no, name, and admission_date. For reg_no and name, it is defined that none of the values in that column can be NULL but the admission_date column is not defined with NOT NULL constraint, so it can contain NULL values.
It is considered good practice to add the NOT NULL constraint to every column in a table. We can also add the NOT NULL constraint to an existing column.
Add NOT NULL constraint to an existing column
The steps to add the NOT NULL constraint to an existing column are:
- Check for any NULL value in that column.
- If there is no NULL value, skip the next step.
- Update the NULL values with non-NULL values, if there are any.
- Modify the column definition with a NOT NULL constraint.
For example -
In the above table students, admission_date column may contain null values. Let's add the NOT NULL constraint to this column.
First, check for the NULL value. If there is any then update every NULL value with non - NULL values in this column.
UPDATE students
SET
admission_date = '2021-08-01'
WHERE
admission_date IS NULL;Now, the column doesn't contain any NULL value. Let's modify the column definition now.
The syntax for this is:
ALTER TABLE table_name
CHANGE
column_name
new_column_name column_definition;
In the above syntax, column_name and new_column_name may be the same or not.
ALTER TABLE students
CHANGE
admission_date
admission_date DATE NOT NULL;The above statements after the execution add the NOT NULL constraint to the admission_date column.
We can also drop the NOT NULL constraint from a column.
Drop NOT NULL constraint from a column
The syntax to drop the NULL constraint from a column is shown below.
ALTER TABLE
MODIFY
column_name
column_definition;Let's drop the NOT NULL constraint from the name column of the students table.
ALTER TABLE
MODIFY
name
name VARCHAR(255);The above statements after execution drop the NOT NULL constraint from the name column. It can be seen that we can drop the NOT NULL constraint by redefining the column definition without the NOT NULL constraint.
The PRIMARY KEY constraint in MySQL
A primary key is a column or a set of columns that uniquely defines each row or tuple in a table.
- There can be only one primary key in a table.
- The primary key can't contain NULL values.
- If we try to insert NULL value to a primary key column, it will cause an error.
- A primary key must contain unique values. For a set of columns, the combination of columns must be unique.
Primary Key definition in CREATE TABLE
The primary key can be defined while creating a table in a CREATE TABLE statement.
Syntax:
Single column as primary key
CREATE TABLE table_name(
column_name datatype PRIMARY KEY
);A set of columns as primary key
CREATE TABLE table_name(
column_name datatype,
.....,
PRIMARY KEY (column_list)
);For example - Let's make reg_no the primary key of the students table given above.
CREATE TABLE students(
reg_no INT PRIMARY KEY
);Add primary key constraints using ALTER TABLE
We can add a primary key to an existing table using ALTER TABLE statement.
Syntax:
ALTER TABLE table_name
ADD PRIMARY KEY(column_list);For example -
ALTER TABLE students
ADD PRIMARY KEY(reg_no, name);The FOREIGN KEY constraint in MySQL
A foreign key is a column or set of columns in a table that references to a column or set of columns of another table. It helps in maintaining links among the tables. Typically, the referenced column or set of columns of another table is the primary key of that table. The referenced table or the table with the primary key is called the parent table and referencing table with the foreign key is called the child table.
A table can have one or more foreign keys and each foreign key references the primary key of other different parent tables.
Sometimes, the parent table and child table can be the same when the foreign key references the primary key of the same table.
The syntax of the foreign key constraint used with CREATE TABLE or ALTER TABLE is shown below.
[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_list)
REFERENCES parent_table(column_list)
[ON DELETE reference_option]
[ON UPDATE reference_option]In the above syntax:
- First, specify the name of the foreign key constraint that we want to create after the CONSTRAINT keyword, it is optional if we omit this MySQL automatically generates a name for this.
- Then, column_list is specified after the FOREIGN KEY keyword and the foreign_key_name is optional.
- Then, the parent table and the list of columns of the parent table are specified after the REFERENCES keyword.
- Then, the reference option is specified after the ON DELETE and ON UPDATE clause to maintain the referential integrity between the child and parent tables.
These are the reference options in MySQL.
- RESTRICT - It restricts the deletion of the rows from the parent table having matching rows in the child table.
- CASCADE - It updates/deletes the values of the matching rows in the child table if a row in the parent table is updated/deleted.
- SET NULL - It set the value of the foreign key to NULL if the matching row in the parent table is deleted.
- NO ACTION - It is the same as the RESTRICT.
If the ON DELETE and ON UPDATE clause is not specified, the default action is RESTRICT.
Example of foreign key -
Let's create a table named result as shown below.
CREATE TABLE result(
roll_no INT,
reg_no INT,
marks INT
FOREIGN KEY (reg_no)
REFERENCES students(reg_no)
);In the above example, the students table is the parent table and the result is the child table. The reg_no in the result table is the foreign key column referencing the reg_no column in the students table.
DROP foreign key constraints in MySQL
We can drop the foreign key constraint using the ALTER TABLE statement. The syntax for this is shown below.
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name; In the above syntax:
- The table_name is the name of the table from which we are dropping the foreign key constraint.
- The constraint name is then specified after the DROP FOREIGN KEY keywords.
UNIQUE constraint in MySQL
The UNIQUE constraint is used in MySQL to ensure uniqueness in the values of a column or set of columns in a table. When a duplicate value is inserted in a column with a unique constraint, MySQL rejects that insertion and issues an error.
Add UNIQUE KEY in CREATE TABLE statement
The unique key can be defined while creating a table using CREATE TABLE statement. The syntax for this is shown below.
Syntax:
Add the UNIQUE constraint to a single column
CREATE TABLE table_name(
column_name data_type UNIQUE
);In the above syntax, the unique keyword is added after the column definition of the column in which it is added.
Add the UNIQUE constraint to a set of columns
CREATE TABLE table_name(
column_name1 data_type,
column_name2 data_type,
UNIQUE (column_list)
);In the above syntax, the UNIQUE keyword with the list of columns is added at the end.
For example -
CREATE TABLE students(
reg_no INT NOT NULL,
name VARCHAR(255) NOT NULL,
admission_date DATE,
UNIQUE (reg_no, name)
);Add the UNIQUE constraint to an existing table
The syntax for adding the UNIQUE constraint to an existing table is shown below.
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column_list);Drop UNIQUE constraint from a column in a table
The UNIQUE constraint can be dropped from a column of a table using the following syntax.
ALTER TABLE table_name
DROP INDEX index_name;or
DROP index_name ON table_name;DEFAULT constraint in MySQL
The DEFAULT constraint is used in MySQL to specify a default value for a column. When a value is not inserted into a column having a DEFAULT constraint, the specified DEFAULT value is inserted by MySQL.
The syntax for this is shown below.
Syntax:
column_name data_type DEFAULT default_value;In the above syntax:
- default_value is specified after the DEFAULT keyword in the column definition.
- The default value must be a literal constant like a number or a string.
Set DEFAULT constraint in CREATE TABLE statement
The DEFAULT constraint can be set while creating a table. For example:
CREATE TABLE result
(
reg_no INT,
name VARCHAR(255),
marks INT DEFAULT 0
);Add DEFAULT constraint to an existing table
We can add DEFAULT constraint to an existing table also using the following syntax.
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;Remove DEFAULT constraint from a column
To remove the DEFAULT constraint from a column in a table, we use the following syntax.
ALTER TABLE table_name
ALTER column_name DROP DEFAULT;In the above syntax, the DROP DEFAULT keyword is used after the column_name from which the DEFAULT constraint is being dropped.
For example -
ALTER TABLE result
ALTER marks DROP DEFAULT;In the above example, the above statement will drop the DEFAULT constraint from the marks column in the result table.
