What is Constraints in TSQL?

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.Constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Types of Constraints.

·         NOT NULL, This specify that column does not access NULL values.

·         CHECK constraints enforce domain integrity by limiting the values that can be put in a column. A check constraint can be Boolean values (True, false or unknown). We can add search condition which will be applied while we do insert or update operation on a column. This also enforce that inserted or updated values must be in range which is specified within check.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

·         UNIQUE constraints enforce the uniqueness of the values in a set of columns. This enforce no two rows in a table can have same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow NULL as one of the unique values.

·     PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

‘   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

•          FOREIGN KEY constraints identify and enforce the relationships between tables.

A foreign key in one table points to a candidate key in another table. (A column or combination    of columns that qualify as a primary key value is referred to as a candidate key.)

CREATE TABLE order_part

  (

order_nmbr      int,

part_nmbr      int

             FOREIGN KEY REFERENCES part_sample(part_nmbr)

            ON DELETE NO ACTION,

          qty_ordered      int

);

GO

You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point.

 The ON DELETE clause has the following options:

a.  NO ACTION specifies that the deletion fails with an error.

b.  CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also  deleted.

c.  SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.

d.  SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value

The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.