SQL | CHECK Constraint
The CHECK constraint is used to limit the values/value range that can be placed in a column.
If you define a CHECK constraint on a column it will allow only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
If the value being added to an attribute of a tuple violates the check constraint, the check constraint evaluates to false and the corresponding update is aborted.
Add Check Constraint on New Table
CONSTRAINT <constraint_name> CHECK(<boolean_expression>)
CREATE TABLE Employee (
EmployeeID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
GENDER VARCHAR(9),
PRIMARY KEY(ID),
check(GENDER in ('Male', 'Female', 'Unknown'))
);
CREATE TABLE student(
StudentID INT NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
GENDER VARCHAR(9),
PRIMARY KEY(ID),
check(Age >= 17)
);
-- If, however following SQL statement is executed,
-- There won’t be any database update and as the age < 17 years.
INSERT INTO student(STUDENTID, LastName, FirstName, AGE, GENDER)
VALUES (1, 'Talwar', 'Deepak', 16, 'Male');
CREATE TABLE products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
);
-- Now if we try to Insert following will get following error
INSERT INTO products(product_name, unit_price)
VALUES ('Sun Glasses', 0);
-- The INSERT statement conflicted with the CHECK constraint "positive_price".
-- The conflict occurred in database "Prctice", table "dbo.products", column 'unit_price'.
alter table TABLE_NAME modify COLUMN_NAME check(Predicate);
-- Giving variable name to check constraint:
alter table TABLE_NAME add constraint CHECK_CONST check (Predicate);
Remove check constraint
alter table TABLE_NAME drop constraint CHECK_CONSTRAINT_NAME;
View existing constraints on a particular table
SELECT *
FROM information_schema.table_constraints
WHERE table_name = 'employee';
