CHECK Constraints and Data Validation

A CHECK condition rejects FALSE, but SQL also has UNKNOWN for comparisons involving NULL. Add NOT NULL when a value must always be present; the email pattern below is only a simple example, not full email validation.

1. What Are SQL Constraints?

Definition:

SQL Constraints are rules applied to table columns to ensure data accuracy, validity, and reliability.

Constraints prevent invalid or inconsistent data from entering the database.

2. When Do We Use Constraints?

Constraints can be added:

During table creation
Later using ALTER TABLE

3. Types of Constraints (Overview)

You will learn all constraints later, but today focuses on CHECK.

Common constraints include:

CHECK
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT

4. CHECK Constraint

Purpose:

Validates that the column value meets a specific condition.

If the value violates the rule → SQL throws an error.

Usage Cases:

Validating status values
Checking ranges (age > 0)
Checking formats (email LIKE pattern)
Checking date logic (end_date > start_date)

5. CHECK Constraint Example – orders Table

5.1 Create Table (Initial)

CREATE TABLE orders (
  order_id INT,
  order_item_id INT,
  order_date DATE,
  customer_id INT,
  order_status VARCHAR(20),
  product_id INT,
  quantity INT,
  product_price FLOAT,
  total_price FLOAT
);

Insert invalid value:

INSERT INTO orders VALUES
(1, 1, '2013-07-25', 11599, 'CLOSE', 957, 1, 299.98, 299.98);

(CLOSE is invalid; not in allowed statuses)

6. Adding CHECK Constraint Using ALTER TABLE

Valid order statuses:

CLOSED
PENDING_PAYMENT
COMPLETE
PROCESSING
ON_HOLD
SUSPECTED_FRAUD
PENDING

ALTER TABLE orders
ADD CHECK (order_status IN
('CLOSED','PENDING_PAYMENT','COMPLETE','PROCESSING',
 'ON_HOLD','SUSPECTED_FRAUD','PENDING'));

To see constraints:

SHOW CREATE TABLE orders;

7. Dropping a CHECK Constraint

ALTER TABLE orders
DROP CHECK orders_chk_1;

Then adding it back with a custom name:

ALTER TABLE orders
ADD CONSTRAINT CHK_OrderStatus
CHECK (order_status IN ('CLOSED','PENDING_PAYMENT','COMPLETE',
'PROCESSING','ON_HOLD','SUSPECTED_FRAUD','PENDING'));

8. Adding CHECK During Table Creation

CREATE TABLE orders (
  order_id INT,
  order_item_id INT,
  order_date DATE,
  customer_id INT,
  order_status VARCHAR(20)
    CHECK (order_status IN ('CLOSED','PENDING_PAYMENT','COMPLETE',
                            'PROCESSING','ON_HOLD','SUSPECTED_FRAUD','PENDING')),
  product_id INT,
  quantity INT CHECK (quantity <= 50),
  product_price FLOAT,
  total_price FLOAT
);

Invalid Insert Example

INSERT INTO orders VALUES
(1,1,'2013-07-25',11599,'CLOSE',957,52,299.98,NULL);

Fails because:

order_status = 'CLOSE' (invalid)
quantity = 52 (violates quantity <= 50)

9. CHECK Constraint Examples on Other Tables

9.1 Age Constraint

CREATE TABLE persons (
  person_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT CHECK (age > 0)
);

Invalid:

INSERT INTO persons VALUES (1, 'Raj', 'Sharma', -5);

9.2 Email Format Constraint

CREATE TABLE users (
  user_id INT,
  username VARCHAR(50),
  email VARCHAR(100),
  CHECK (email LIKE '%@%.%')
);

Valid Email:

INSERT INTO users VALUES (1, 'sumitm', 'sumit@trendytech.in');

9.3 Date Comparison Constraint

Ensures project end_date is after start_date.

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR(100),
  start_date DATE,
  end_date DATE,
  CHECK (end_date > start_date)
);

Valid:

INSERT INTO projects VALUES
(1, 'Project Alpha', '2023-01-01', '2023-12-31');