SQL Reference

CHECK

CHECK is a constraint in SQL that allows you to specify a condition on the values entered into a table. It ensures that all values in a column satisfy certain criteria specified during table creation or modification. If an attempt is made to insert or update a value in a column that would violate the CHECK constraint, SQL will return an error and reject the change. CHECK can enforce domain integrity.

Example

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(100),
    Age INT,
    CHECK (Age >= 5 AND Age <= 20)
);

Output

Query OK, 0 rows affected, 1 warning (0.01 sec)

Explanation

The SQL code snippet above creates a Students table with three columns: ID, Name, and Age. The CHECK constraint ensures that the Age entered during the data injection into this table is always between 5 and 20, inclusive.

Example

CREATE TABLE Order(
    OrderID int PRIMARY KEY,
    Quantity int,
    CHECK (Quantity >= 1)
);

Output

CREATE TABLE

Explanation

In this example, a CHECK constraint is used to ensure that the Quantity of any Order created must be greater than or equal to 1. Thus, any attempt to insert into the Order table a Quantity value that is less than 1 will fail, enforcing data integrity.

Example

CREATE TABLE Employees (
    ID int NOT NULL,
    Age int,
    Check (Age BETWEEN 20 AND 65)
);

Output

/* This section will not have an output as the SQL statement is for table creation with appropriate constraints. No query for data manipulation or selection is executed, hence no visual output */

Explanation

The above SQL code block creates a table named “Employees”. The table has two columns - “ID”, which cannot have NULL values, and “Age”. The CHECK constraint enforces that any value inserted or modified in the “Age” column must be between 20 and 65.

Example

CREATE TABLE Students (
    ID int,
    Age int,
    CHECK (Age >= 18)
);

Output

Table STUDENTS created.

Explanation

The CHECK constraint ensures that all values in the Age column of the Students table are greater than or equal to 18. This check is performed whenever data is inserted or updated in the Age column. If a value that does not meet this condition is provided, the operation will be rejected and an error will be generated by Oracle.

Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Age INT,
    Salary REAL,
    CHECK (Age >= 18 AND Salary >= 15000)
);

Output

Table 'Employees' has been successfully created.

Explanation

In the above code, a new table Employees is created with a CHECK constraint. The CHECK constraint ensures the Age value is always greater than or equal to 18 and the Salary value is always greater than or equal to 15000. Any attempt to insert a record not complying with these restrictions will be rejected.