The CHECK constraint in SQL is used to limit the values that a column can hold in the creation of the table. The Check constraint is basically mentioned in CREATE TABLE command in SQL. If the value added column, if it violates the constraint then it evaluates to false and the value is aborted.
- Syntax to CHECK constraint –
CREATE TABLE table_name ( column1 datatype , column2 datatype ,column3 datatype,....., columnN datatype ,
CHECK(column1 in ('A', 'B')) );
The CHECK constraint in the above syntax restricts column1 to ‘A’ or ‘B’. If any new value gets added column that doesn’t belong to any of the restricted values, then the value gets aborted.
Example – Create a table as EMPLOYEES with columns as Emp_ID, Emp_Name, Department, and Gender, and restrict the Gender column to (Male, Female, Other).
CREATE TABLE Employees
( Emp_ID INT NOT NULL,
Emp_Name VARCHAR(30) NOT NULL,
Department VARCHAR(20) NOT NULL,
GENDER VARCHAR(9),
PRIMARY KEY(Emp_ID),
CHECK(GENDER in ('Male', 'Female', 'Other')) );
Now, this Employee table is created in the database. Let’s check the constraint by entering other values which do not match the restricted values in the Gender.
INSERT INTO Employees( Emp_ID , Emp_Name, Department , GENDER ) VALUES(1, 'Simon', 'Accounts' , 'Binary');
Here, in “Gender” a value other than the restricted value is entered. The value will be aborted and the execution of the above query will give an error message in the console.
The output after implementing the above query –
Other SQL topics to check out:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
