SQL Constraints (Constraints)

In this tutorial, you will learn how to use SQL constraints.

What are constraints?

Constraints are restrictions on one or more columns of a table to limit the type of values that can be stored in the column. Constraints provide a standard mechanism to maintain the accuracy and integrity of data within a database table.

There are several types of constraints in SQL, including:

• NOT NULL

• PRIMARY KEY

• UNIQUE

• DEFAULT

• FOREIGN KEY

• CHECK

Now, let's discuss each constraint in detail.

NOT NULL constraint

The NOT NULL constraint specifies that the specified column does not accept NULL values.

This means that if NOT NULL imposes a constraint on a column, then you must insert a new row in the table without adding a non-NULL value for that column.

以下SQL语句创建一个名为person'sThe table, which has four columns, of which three columnsid,nameemployees (employees)table, andNULL values are not accepted.

column to create a default value.
　　　　id INT NOT NULL,
　　　　) id INT NOT NULL PRIMARY KEY,3name VARCHAR(
　　　　0) NOT NULL,
　　　　) birth_date DATE,15) NOT NULL
);

Primary key constraint (PRIMARY KEY)

The PRIMARY KEY constraint identifies the column or set of columns that uniquely identify the row values in the table. No two rows in the table can have the same primary key value. Similarly, you cannot input NULL values in the primary key column.

以下SQL语句创建一个名为The following SQL statement creates a table namedpersonsidThe column specified as the primary key. This means that the field does not allow NULL or duplicate values.

column to create a default value.
　　　　CREATE TABLE persons (
　　　　) id INT NOT NULL PRIMARY KEY,3name VARCHAR(
　　　　0) NOT NULL,
　　　　) birth_date DATE,15) NOT NULL
);

Unique constraint (UNIQUE)

The UNIQUE constraint restricts one or more columns in a table to contain unique values.

Although both UNIQUE constraints and PRIMARY KEY constraints enforce uniqueness, UNIQUE should be used when you want to enforce uniqueness on a column or a combination of columns (not the primary key) rather than a constraint.

以下SQL语句创建一个名为The following SQL statement creates a table namedpersonstable, andphone

column to create a default value.
　　　　CREATE TABLE persons (
　　　　) id INT NOT NULL PRIMARY KEY,3name VARCHAR(
　　　　0) NOT NULL,
　　　　) birth_date DATE,15column specified as unique. This means that the field does not allow duplicate values.
);

Multiple UNIQUE constraints can be defined on a single table, while only one PRIMARY KEY constraint can be defined on a single table. Moreover, unlike PRIMARY KEY constraints, UNIQUE constraints allow NULL values.

DEFAULT constraint (DEFAULT)

DEFAULT constraint specifies the default value for a column.column's default value is whenINSERT

statement, the database engine will insert a value into the column when a specific value is not explicitly assigned. The following SQL statement is for/CountryRegion

column to create a default value.
　　　　CREATE TABLE persons (
　　　　) id INT NOT NULL PRIMARY KEY,3name VARCHAR(
　　　　0) NOT NULL,
　　　　) birth_date DATE,15phone VARCHAR(
　　　　) NOT NULL UNIQUE,3country VARCHAR(
);

If a table column is defined as NOT NULL but a default value is assigned to it, then a new row can be inserted into the table without explicitly assigning a value to the column in the INSERT statement.

FOREIGN KEY constraint (FOREIGN KEY)

foreign key (FK) is a column or a combination of columns used to establish and strengthen relationships between data in two tables.This showsemployees (employees)Departmentanddepartments(employeestabledept_idcolumn is an example of the relationship betweendepartmentstable's primary key column. Therefore,dept_id that matches thecolumnnamed employeestable's foreign key forDepartmenttable.

In MySQL, you can create a FOREIGN KEY when creating a table by defining constraints. The following statement creates a foreign key inemployeestabledept_idto establish a foreign key on thedepartmentstabledept_idcolumn.

CREATE TABLE employees (
　　　　emp_id INT NOT NULL PRIMARY KEY,
　　　　emp_name VARCHAR(55) NOT NULL,
　　　　hire_date DATE NOT NULL,
　　　　salary INT
　　　　dept_id INT,
　　　　FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CHECK constraint (CHECK)

CHECK constraints are used to limit the values that can be placed in a column.

For example, you can create a CHECK constraint to limit the value range of the salary column, which only allows values between3to10between 0,000 and 0,000. This can prevent the input of salaries that exceed the normal salary range. Here is an example:

CREATE TABLE employees (
　　　　emp_id INT NOT NULL PRIMARY KEY,
　　　　emp_name VARCHAR(55) NOT NULL,
　　　　hire_date DATE NOT NULL,
　　　　salary INT NOT NULL CHECK (salary >=　3000 AND salary <=　10000),
　　　　dept_id INT,
　　　　FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);