SQL Check

The CHECK constraint is used to limit the value range that can be placed in a column. If the condition evaluates to FALSE, the data is rejected. This ensures data validity by enforcing rules at the column or table level.

SQL Check Syntax

CREATE TABLE table_name (
  column_name datatype CHECK (condition)
);

CREATE TABLE table_name (

  column_name datatype CHECK (condition)

);

You can also define a CHECK constraint on multiple columns:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  CHECK (column1 > column2)
);

CREATE TABLE table_name (

  column1 datatype,

  column2 datatype,

  CHECK (column1 > column2)

);

SQL Check Example

SQL Check on Create Table

This query applies a CHECK constraint while creating the table, ensuring that the date of birth (dob) is not in the future:

CREATE TABLE PatientsWithDOBCheck (
  patient_id INTEGER PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  dob DATE CHECK (dob <= DATE('now')),
  gender CHAR(1) NOT NULL,
  contact_number VARCHAR(15)
);

CREATE TABLE PatientsWithDOBCheck (

  patient_id INTEGER PRIMARY KEY,

  first_name VARCHAR(50) NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  dob DATE CHECK (dob <= DATE('now')),

  gender CHAR(1) NOT NULL,

  contact_number VARCHAR(15)

);

SQL Check on Alter Table

SQLite does not support adding CHECK constraints using ALTER TABLE. The only workaround is to recreate the table with the new CHECK constraint and copy the data from the original table.

Steps:

1. Create a new table with the desired CHECK constraint:

CREATE TABLE PatientsNew (
  patient_id INTEGER PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  dob DATE NOT NULL,
  gender CHAR(1) NOT NULL CHECK (gender IN ('M', 'F')),
  contact_number VARCHAR(15)
);

CREATE TABLE PatientsNew (

  patient_id INTEGER PRIMARY KEY,

  first_name VARCHAR(50) NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  dob DATE NOT NULL,

  gender CHAR(1) NOT NULL CHECK (gender IN ('M', 'F')),

  contact_number VARCHAR(15)

);

2. Copy data from the existing table:

INSERT INTO PatientsNew (patient_id, first_name, last_name, dob, gender, contact_number)
SELECT patient_id, first_name, last_name, dob, gender, contact_number FROM Patients;

INSERT INTO PatientsNew (patient_id, first_name, last_name, dob, gender, contact_number)

SELECT patient_id, first_name, last_name, dob, gender, contact_number FROM Patients;

3. Drop the old table (optional — do with caution):

DROP TABLE Patients;

DROP TABLE Patients;

4. Rename the new table:

ALTER TABLE PatientsNew RENAME TO Patients;

ALTER TABLE PatientsNew RENAME TO Patients;

SQL Check Drop a Check Constraint

SQLite does not support dropping a CHECK constraint directly.

If needed, you must:

• Create a new table without the constraint
• Move the data over
• Drop the old table
• Rename the new one

SQL Check Labs