-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
SQL Check
Whenever a check constraint is applied to the table’s column, and the user wants to insert the value in it, then the value will first be checked for certain conditions before inserting the value into that column.
For example: if we have an age column in a table, then the user will insert any value of his choice. The user will also enter even a negative value or any other invalid value. But, if the user has applied check constraint on the age column with the condition age greater than 18. Then in such cases, even if a user tries to insert an invalid value such as zero or any other value less than 18, then the age column will not accept that value and will not allow the user to insert it due to the application of check constraint on the age column.
Example:
CREATE TABLE TableName (ColumnName1 datatype CHECK (ColumnName1 Condition), ColumnName2 datatype,…., ColumnNameN datatype);
The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that the age of a person must be 18, or older:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=’Sandnes’)
);
To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:
ALTER TABLE Persons ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=’Sandnes’);
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons DROP CHECK CHK_PersonAge;
ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
Course Video
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1. create table of voter and add voter id, name, address, contact, and age in which age column cannot contain the value less than 18 by using check constraint.
Output on SQL server
Note: If you try to insert the value less than 18 in age column you will face the error
2. Create the table with 5 column and add check constrain on two column that the column should not contain null value.
Output on SQL server
Hint: Check constrain is applied on amount and quantity column
3. Add check constrain on product column of above table by using alter table statement
4. create a table and add check constraint of user define name
5. Write a sql query to delete the check constrain from the table
YouTube Reference :
1) SQL Check in Hindi/Urdu
2) SQL Check in English
