CHECK Constraint FAQS

1. What is a CHECK constraint in Oracle?

A CHECK constraint ensures column values meet a specified condition.

·        Enforces data integrity

·        Works on single or multiple columns

·        Evaluated on INSERT or UPDATE

Example:

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    name VARCHAR2(100),

    salary NUMBER,

    CONSTRAINT salary_chk CHECK (salary > 0)

);

·        salary must be greater than 0.

2. What problem does a CHECK constraint solve?

·        Prevents invalid data (e.g., negative salary, wrong age)

·        Enforces business rules at the database level

·        Reduces application-side validation

3. Can a CHECK constraint involve multiple columns?

Yes.

Example:

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    quantity NUMBER,

    unit_price NUMBER,

    CONSTRAINT total_chk CHECK (quantity * unit_price <= 10000)

);

·        Ensures total order ≤ 10,000

·        Supports multi-column expressions

4. Can CHECK constraint reference another table?

No. Use FOREIGN KEY for cross-table validation.

5. Can CHECK constraints include functions or subqueries?

·        Deterministic functions (e.g., LENGTH, SYSDATE) allowed

·        Subqueries not allowed

Example:

CONSTRAINT name_len_chk CHECK (LENGTH(name) >= 3)

6. What happens if a CHECK constraint is violated?

Oracle raises:

ORA-02290: check constraint (SALARY_CHK) violated

Example:

INSERT INTO employees VALUES (1, 'John', -500);

·        Data violating the condition is rejected.

7. Can a CHECK constraint allow NULL values?

Yes. NULLs are ignored unless combined with NOT NULL.

8. Can CHECK constraints be disabled?

Yes:

ALTER TABLE employees DISABLE CONSTRAINT salary_chk;

ALTER TABLE employees ENABLE CONSTRAINT salary_chk;

9. Can a CHECK constraint be DEFERRABLE?

Yes, rarely used:

ALTER TABLE employees

ADD CONSTRAINT salary_chk CHECK (salary > 0)

DEFERRABLE INITIALLY DEFERRED;

·        Validated at COMMIT

10. Can CHECK constraints be named or unnamed?

·        Named → easy management

·        Unnamed → system-generated

Example:

CONSTRAINT chk_salary_positive CHECK (salary > 0)

11. How to add a CHECK constraint to an existing table?

ALTER TABLE employees

ADD CONSTRAINT chk_salary_positive

CHECK (salary > 0);

12. How to drop a CHECK constraint?

ALTER TABLE employees

DROP CONSTRAINT chk_salary_positive;

13. How to view CHECK constraints in a table?

SELECT constraint_name, search_condition, status

FROM user_constraints

WHERE table_name = 'EMPLOYEES' AND constraint_type = 'C';

·        C → CHECK constraint

14. Common mistakes

·        Referencing another table → Not allowed

·        Using non-deterministic functions

·        Forgetting NOT NULL

·        Overly complex expressions

·        Bulk inserts without disabling constraints

15. Performance impact

·        Positive: Ensures data integrity

·        Negative: Slight overhead on INSERT/UPDATE; complex expressions can slow DML

16. Best practices

·        Name constraints for management

·        Keep expressions simple & deterministic

·        Combine with NOT NULL if mandatory

·        Avoid cross-table validation

·        Use DEFERRABLE for batch operations if needed

17. Real-world examples

1.    Age ≥ 18:

CONSTRAINT chk_age CHECK (age >= 18)

2.    Status limited to specific values:

CONSTRAINT chk_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'PENDING'))

18. Interview Tip

“A CHECK constraint enforces a condition on column values, ensuring only valid data is allowed. It can include expressions, multiple columns, or IN lists, but cannot reference other tables. Named constraints are easier to manage, and best practices include keeping expressions simple, combining with NOT NULL when required, and using DEFERRABLE for batch operations.”