Related knowledge about SQL CHECK constraints

SQL CHECK Constraints are very important for php, this article will explain its related knowledge.

CHECK constraints are used to limit the range of values ​​in a column.

If a CHECK constraint is defined on a single column, only specific values ​​are allowed for that column.

If a CHECK constraint is defined on a table, this constraint will limit the values ​​in a specific column.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint for the "Id_P" column when the "Persons" table is created. The CHECK constraint states that the "Id_P" column must contain only integers greater than 0.

My SQL:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),CHECK (Id_P>0))

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar (255),
City varchar(255)
)

If you need to name the CHECK constraint and define CHECK constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL ,
FirstName varchar(255),
Address varchar(255),
City varchar(255),CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes'))

SQL CHECK Constraint on ALTER TABLE

If you create a CHECK constraint for the "Id_P" column when the table already exists, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE PersonsADD CHECK (Id_P>0)

If you need to name CHECK constraints and define CHECK constraints for multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

Revoke CHECK constraint

To revoke CHECK constraints, please use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE PersonsDROP CONSTRAINT chk_Person

This article explains the related operations of CHECK constraints. For more learning materials, please pay attention to the php Chinese website.

Related recommendations:

Related knowledge about SQL UNIQUE constraints

Related knowledge about SQL NOT NULL constraints

How to use the SQL CREATE TABLE statement

The above is the detailed content of Related knowledge about SQL CHECK constraints. For more information, please follow other related articles on the PHP Chinese website!