CHECK - SQL Tutorial

SQL CHECK Keyword

The CHECK constraint is used in SQL to limit the values that can be inserted into a column. It ensures that data entered into a table meets specific rules and helps maintain data integrity.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the EngineSize column when the Vehicles table is created. The constraint ensures that no vehicle with an engine size below 100 cc can be added.

MySQL:

CREATE TABLE Vehicles (
    EngineSize INT,
    CHECK (EngineSize >= 100)
)

SQL
​x
 
CREATE TABLE Vehicles (
    EngineSize INT,
    CHECK (EngineSize >= 100)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Vehicles1 (
    EngineSize INT CHECK (EngineSize >= 100)
)

SQL
 
CREATE TABLE Vehicles1 (
    EngineSize INT CHECK (EngineSize >= 100)
)

Note: In SQLite including in the TechKubo playground CHECK constraints are supported but must be defined during table creation Only database systems like MySQL and SQL Server allow adding or dropping CHECK constraints after creation using ALTER TABLE.

SQL CHECK with Named Constraint on Multiple Columns

You can name a CHECK constraint and define it using multiple columns. The following example ensures that only motorcycles with an engine size of at least 100 cc and brand equal to ‘Yamaha’ are allowed.

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Vehicles2 (
    EngineSize INT,
    Brand VARCHAR(255),
    CONSTRAINT CHK_EngineBrand CHECK (EngineSize >= 100 AND Brand = 'Yamaha')
)

SQL
 
CREATE TABLE Vehicles2 (
    EngineSize INT,
    Brand VARCHAR(255),
    CONSTRAINT CHK_EngineBrand CHECK (EngineSize >= 100 AND Brand = 'Yamaha')
)

Note: SQLite including the TechKubo playground does support named CHECK constraints during CREATE TABLE However you cannot add or drop named constraints later using ALTER TABLE.

SQL CHECK on ALTER TABLE

You can also add a CHECK constraint to an existing table using ALTER TABLE. The example below ensures that only vehicles with a horsepower greater than or equal to 50 are allowed.

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Vehicles
ADD CHECK (Horsepower >= 50)

SQL
 
ALTER TABLE Vehicles
ADD CHECK (Horsepower >= 50)

With a named constraint that applies to both horsepower and brand

ALTER TABLE Vehicles
ADD CONSTRAINT CHK_PowerBrand CHECK (Horsepower >= 50 AND Brand = 'Honda')

SQL
 
ALTER TABLE Vehicles
ADD CONSTRAINT CHK_PowerBrand CHECK (Horsepower >= 50 AND Brand = 'Honda')

Note: These commands will not work in SQLite. SQLite does not support adding constraints to existing tables using ALTER TABLE. Constraints must be defined when the table is created.

SQL DROP a CHECK Constraint