Lecture Notes: Check Constraint in SQL

Introduction to Check Constraint

• Purpose: Limits what values can be placed in a column.
• Example Scenario: Minimum hourly wage requirement based on regional laws.

Implementing Check Constraint

1. Creating a Table with Check Constraint:

   • Define the table (e.g., employees).
   • Add a check at the end of the table definition to enforce the constraint.
   • Example Syntax: CHECK (hourly_pay >= 10)
   • Naming the Constraint:
     • Use CONSTRAINT keyword followed by a name (e.g., chk_hourly_pay).
     • Facilitates easy identification for future modifications or deletion.

2. Adding Check Constraint to Existing Table:

   • Use ALTER TABLE statement.
   • Syntax: ALTER TABLE employees ADD CONSTRAINT chk_hourly_pay CHECK (hourly_pay >= 10)

Testing Check Constraint

• Inserting Data: Attempt to insert an employee with an hourly pay less than the constraint.

  • Example: INSERT INTO employees VALUES (6, 'Sheldon', 'Plankton', 5, '2023-01-07')
  • Outcome: Violates check hourly pay constraint, hence insertion fails.

• Successful Insertion: Adjust to meet constraint:

  • Change hourly pay to $10 and reinsert.
  • Insertion succeeds.

Deleting Check Constraint

• Syntax: ALTER TABLE employees DROP CONSTRAINT chk_hourly_pay

Conclusion

• Utility: Check constraint is a powerful method for enforcing data integrity within SQL databases.
• Application: Especially useful in scenarios requiring compliance with specific data rules (e.g., regional laws).