112 SQL CHECK

#Edu #SQL

The current page explains the SQL CHECK constraint, which is used to restrict the range of values allowed in a column of a table. Key points include:

The CHECK constraint ensures only specified values that meet a certain condition can be entered into a column.
You can define a CHECK constraint:
- On a single column, such as requiring "Age" to be 18 or older.
- At the table level, to enforce rules across multiple columns (for example, "Age" must be 18 or older and "City" must be 'Sandnes').
Syntax is provided for both creating tables with CHECK constraints and adding them to existing tables using ALTER TABLE.
There are differences in how to drop (remove) a CHECK constraint between SQL Server/Oracle/MS Access and MySQL.
The page includes practical SQL code examples and an interactive quiz question about the purpose of the CHECK constraint, highlighting that its main use is to limit the value range that can be placed in a column.[1]

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a column it will allow only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

MySQL:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    CHECK (Age>=18)  
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int CHECK (Age>=18)  
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255),  
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')  
);

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons  
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons  
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons  
DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons  
DROP CHECK CHK_PersonAge;