How do you create a table with a CHECK constraint to enforce data integrity?
EveClark
Posted by EveClark
Last Updated: July 10, 2024
table constraint
Creating a table with a CHECK constraint is a way to enforce data integrity by ensuring that specific conditions are met for the data being inserted or updated in the table. The CHECK constraint allows you to define rules that the values in one or more columns must satisfy. Here's how you can create a table with a CHECK constraint in SQL:
Basic Steps to Create a Table with a CHECK Constraint
1. Define the Table: Use the CREATE TABLE statement to define a new table. 2. Specify Columns: Define the columns for the table, including their data types. 3. Add the CHECK Constraint: Use the CHECK keyword followed by the condition that must be met.
Example SQL Statement
Here's an example of creating a table called Employees that includes CHECK constraints: 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18 AND Age <= 65),  -- Age must be between 18 and 65
    Salary DECIMAL(10, 2) CHECK (Salary > 0), -- Salary must be greater than 0
    Department VARCHAR(50),
    HireDate DATE CHECK (HireDate <= CURRENT_DATE)  -- HireDate cannot be in the future
);
Explanation of the Example
- EmployeeID: An integer that serves as the primary key for the table. - FirstName and LastName: String fields for the employee's first and last names, respectively; both are mandatory (NOT NULL). - Age: An integer where a CHECK constraint ensures that the age is between 18 and 65. - Salary: A decimal number where a CHECK constraint ensures that the salary is greater than 0. - Department: A string field for the employee's department. - HireDate: A date field with a CHECK constraint ensuring that the hire date is not set to a future date.
Notes on CHECK Constraints
- You can specify multiple CHECK constraints in a table definition. - The constraints can refer to any column within the same row of the table. - If an INSERT or UPDATE operation violates the CHECK constraint, the database will reject the operation and return an error. - Most modern relational databases support CHECK constraints, but it's good to verify compatibility with the specific database system you are using. Using CHECK constraints helps ensure that the data in your tables adheres to specific business rules and validation criteria, thus maintaining data integrity.
Default Image