Predefined value for a column in SQL Server Table object

How to set predefined value for a column in SQL Server Table object

Check constraints contain an expression the database will evaluate when you modify or insert a row. You can use any of the operator (<, >, <=, >=, IN, Like, Between, AND, OR) to build your check constraint expression. Only when the expression in check constraint evaluates to true, you will be allowed to insert or update record in the table whereas if check constraint expression evaluate to false you will not allowed enter/update record in the table.

You can add check constraint for your column in 3 ways: column level, table level or using alter table statement.

1) Column level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

2)Table Level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name,

constraint  chkActivityName  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

3)In Alter Statement

            Alter table ActivityMaster

Add check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

Here in the above example we are having a table called ActivityMaster which contain two column activity_id and activity_name then we are adding check constraint with activity_name which says, the activity_name column cannot have other than  (‘cricket’, ’tt’, ’swimming ’).

Some more use of Check

We can have multiple use of check constraint say salary should be greater than 3000

            Alter table EmployeeSalary

Add check (emp_salary > 3000)

           

You can also use ‘AND’ and ‘OR’ operator with check constraint.

            Alter table EmployeeSalary

Add check (emp_salary  >=  3000 AND emp_salary  <= 90000 )

Two or more column in single Check

You can have multiple columns in a single check constraint like the one below

            Alter table EmployeeSalary

            Add check (emp_salary > 10000 and emp_post like ‘manager’)

                                

Post Reference: Vikram Aristocratic Elfin Share