In SQL, a CHECK constraint is used to enforce domain integrity by limiting the values that can be inserted into a column. It specifies a search condition that must be met for the data to be valid. If the condition evaluates to false for any row when data is inserted or updated, the constraint prevents the operation from completing successfully.
Here's a basic example of how to use a CHECK constraint in a CREATE TABLE statement:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT CHECK (Age >= 18)
);
In this example, the CHECK constraint ensures that the Age column in the Employees table contains values greater than or equal to 18. If an attempt is made to insert or update a row with an Age value less than 18, the operation will fail.
You can also add a CHECK constraint to an existing table using the ALTER TABLE statement:
ALTER TABLE Employees
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);
Additionally, CHECK constraints can reference multiple columns or use more complex conditions using logical operators like AND, OR, and parentheses for grouping.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10, 2),
Discount DECIMAL(5, 2),
CONSTRAINT CHK_Discount CHECK (Discount >= 0 AND Discount <= Price)
);
In this example, the CHK_Discount constraint ensures that the Discount value is greater than or equal to 0 and less than or equal to the Price value. This ensures that the discount does not exceed the product price.