CHECK

What is the CHECK constraint?

• The CHECK constraint ensures that all values in a column satisfy a condition you define.
• The condition is checked on every INSERT and UPDATE. If it evaluates to false, the row violates the constraint and is not entered into the table.
• It is the simplest way to enforce business rules - like 'age must be 18 or more' - directly in the database.
• A CHECK constraint can reference one column or several columns of the same row.

Writing CHECK conditions

• Keep the condition simple and deterministic: comparisons, BETWEEN, IN lists, and basic AND/OR logic.
• The condition cannot contain subqueries, and it cannot reference other tables.
• Name the constraint so violations produce a readable error, e.g. CHK_CUST_AGE.
• Existing rows are validated when you add the constraint - every row must already satisfy it.

Example

• Column-level CHECK:-
  
  CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS CHAR(25), SALARY DECIMAL(18,2), PRIMARY KEY (ID));
• Named table-level CHECK added later:-
  
  ALTER TABLE CUSTOMERS ADD CONSTRAINT CHK_CUST_SAL CHECK (SALARY >= 0);
• Drop a CHECK constraint:-
  
  ALTER TABLE CUSTOMERS DROP CONSTRAINT CHK_CUST_SAL;