MySQL – Constraints
MySQL constraints are statements that can be applied to columns or tables to specify rules for the data that can be entered into a column or table. In other words, constraints are simply limitations that limit the type of data that can be entered. Therefore, they ensure that the data is reliable, consistent, and accurate. A constraint in SQL may take the form of one of the following types. A brief overview of the generic purpose of these constraints is provided below.
| SQL Constraint | Function |
|---|---|
| NOT NULL | This ensures that a column does not accept NULL values. |
| CHECK | This function ensures that a column accepts values within a specified range. |
| UNIQUE | The function ensures that a column does not accept duplicate values. |
| PRIMARY KEY | A row in the table is uniquely identified by this value. The constraint consists of a combination of the NOT NULL and UNIQUE constraints. |
| FOREIGN KEY | This constraint is similar to a primary key constraint. However, it uniquely identifies a row in another table. |
| DEFAULT | This ensures that a default value is set for empty records in the column. |
NOT NULL CONSTRAINT
When a NOT NULL constraint is applied to a column, it ensures that NULL values will not be accepted. MYSQL uses the following syntax for the NOT NULL constraint:
Syntax:
CREATE TABLE table_name( column_name_1 datatype NOT NULL, column_name_2 datatype NOT NULL, column_name_n datatype NOT NULL );
CREATE TABLE table_name: In a database, this statement is used to create a new table. The name of the table should be specified.
column_name1, column_name2, …: The names of the columns you want to create in the table should be specified.
Datatype : The datatype of each column in the table should be specified.
[NULL | NOT NULL]: Indicate whether it is capable of holding NULL values.
CHECK CONSTRAINT
The CHECK constraint ensures that a column will not accept data values outside the specified range when it is applied to a column. CHECK constraints can also be used to ensure that data meets a specified condition, such as 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 );
CREATE TABLE table_name: In a database, this statement is used to create a new table. The name of the table should be specified.
column_name1, column_name2, …: The names of the columns you want to create in the table should be specified.
Datatype : The datatype of each column in the table should be specified.
CHECK(condition_on_column_name_2): The conditions under which data should be entered into a column should be specified.
UNIQUE KEY CONSTRAINT
A unique key is a constraint in SQL that uniquely identifies a record within a data table. A unique key is somewhat similar to a primary key in the sense that both of them ensure the uniqueness of a record. However, unlike a primary key, a unique key can accept NULL values, and it may be used in more than one column of the data table.
Syntax:
CREATE TABLE table_name ( Column_name1 datatype [NULL | NOT NULL] UNIQUE, Column_name2 datatype [NULL | NOT NULL], Column_name3 datatype [NULL | NOT NULL] );
CREATE TABLE table_name: In a database, this statement is used to create a new table. The name of the table should be specified.
column_name1, column_name2, …: The names of the columns you want to create in the table should be specified.
Datatype : The datatype of each column in the table should be specified.
[NULL | NOT NULL]: Indicate whether it is capable of holding NULL values.
UNIQUE : In order to create a unique key constraint, the keyword unique must be written with the column name. This ensures that there are no duplicate values in the column.
PRIMARY KEY CONSTRAINT
The primary key constraint is used to uniquely identify records or rows in a data table. It does not accept NULL values and only 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) );
CREATE TABLE table_name: In a database, this statement is used to create a new table. The name of the table should be specified.
column_name1, column_name2, …: The names of the columns you want to create in the table should be specified.
Datatype : The datatype of each column in the table should be specified.
PRIMARY KEY (column_name_1):You must specify the column name on which the primary key constraint must be applied. It should be a column that is NOT NULL.
FOREIGN KEY CONSTRAINT
Using a foreign key constraint, a record or row in another table can be uniquely identified. This refers to the primary key in the other table. Thus, it serves 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) );
CREATE TABLE table_name: In a database, this statement is used to create a new table. The name of the table should be specified.
column_name1, column_name2, …: The names of the columns you want to create in the table should be specified.
Datatype : The datatype of each column in the table should be specified.
PRIMARY KEY (column_name_1):You must specify the column name on which the primary key constraint must be applied. It should be a column that is NOT NULL.
FOREIGN KEY (column_name_2): Provide the name of the column on which a foreign key constraint must be applied.
REFERENCES table_name_2(column_name_2): Indicate the name of the table and the column on which the foreign key in the first table resides.