Loading Posts...

SQL Check Constraint Example | Check Constraint In SQL Tutorial

SQL Check Constraint Example | Check Constraint In SQL Tutorial

SQL Check Constraint Example | Check Constraint In SQL Tutorial is today’s topic. Check constraint is used for specifying the predicate that every tuple must satisfy in a relation i.e. it is used for limiting the values that a column can hold in a relation. It is used for giving a condition to check the value to be entered into a record. If the condition results in false then that value will not be added to the record.

SQL Check Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows 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.

Some key points of SQL Check Constraint are following.

  1. Check constraint cannot be defined inside the SQL views.
  2. Check constraint only refers to the column of that table in which it is created not in the other table.
  3. A subquery is not allowed in check constraint.
  4. Check constraint is not only defined inside the CREATE TABLE statement but also in an ALTER TABLE statement.

#Syntax: (Create Statement)

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL],
  column2 datatype [ NULL | NOT NULL],
  ...
  CONSTRAINT constraint_name
  CHECK [ NOT FOR REPLICATION] (column_name condition)
);

#Parameters

  1. Table_name: Name of the table in which constraint is to be specified.
  2. Constraint_name: Name of the Constraint. It can be anything.
  3. Column_name: Name of the column in which constraint is to be specified.
  4. Condition: Condition that must be met by the constraint.

Let’s look at the example to understand the above concept.

#EXAMPLE: 1

See the following query.

CREATE TABLE Employee (
        ID INT NOT NULL,
        Name VARCHAR (30) NOT NULL,
        Age INT,
        GENDER VARCHAR (9),
        check (GENDER in ('Male', 'Female'))
);

So, here we have created a table named Employee which contains ID, Name, Age and its gender. Now, when we start entering the values inside the table check constraint in the above SQL command will restrict the gender value up to two categories.

If any other tuple is inserted with gender value other than of these two, then the database operation will be aborted.

#EXAMPLE: 2

CREATE TABLE Student (
        ID INT NOT NULL,
        Name VARCHAR (30) NOT NULL,
        Age INT NOT NULL,
        GENDER VARCHAR (9),
        check (Age >= 18)
);

So, here we have created a table called Student which will contain the data of all students present in the university. The constraint specified here is Age which will not allow the entry of that student whose age is less than 18.

If, however, the following SQL statement is used.

INSERT INTO Student (ID, Name, Age, Gender) 
VALUES (1, 'Roger', 16, 'Male');

This tuple will not be updated in the Student database as age is 16 which is less than 18.

#SYNTAX: (Alter Statement)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name condition);

#PARAMETERS

  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.
  3. Column_name: Name of the column in which constraint is to be specified.
  4. Condition: Condition that must be met by the constraint.

#EXAMPLE

ALTER TABLE Student
ADD CONSTRAINT check_gender
CHECK (Gender IN ('Male', 'Female'));

So, in our example, we have created a constraint named check_gender which will restrict the entry of tuples which does not contain the categories of gender as mentioned above.

#SYNTAX: (Disable Check Constraint)

This is mainly used in Transact-SQL.

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

#PARAMETERS

  • Table_name: Name of the table. ‘
  • Constraint_name: Name of the Constraint. It can be anything.

#EXAMPLE

ALTER TABLE Student
NOCHECK CONSTRAINT check_Gender;

So, the above query will disable the Check constraint.

#SYNTAX: (Enable Check Constraint)

This is mainly used in Transact-SQL.

ALTER TABLE table_name
WITH CHECK CHECK CONSTRAINT constraint_name;

#PARAMETERS

  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.

#EXAMPLE

ALTER TABLE Student
WITH CHECK CHECK CONSTRAINT check_Gender;

So, here in the above query, we have enabled check_gender which was previously disabled as done above.

#SYNTAX: (DROPPING A CHECK CONSTRAINT)

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

#PARAMETERS

  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.

#EXAMPLE

ALTER TABLE Student
DROP CONSTRAINT check_gender;

So, in the above query, we have deleted the constraint named check_gender.

Finally, SQL Check Constraint Example | Check Constraint In SQL Tutorial is over.

The post SQL Check Constraint Example | Check Constraint In SQL Tutorial appeared first on AppDividend.

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

user

The author didnt add any Information to his profile yet

Leave a Comment

Loading Posts...