Constraints in sql server

Are the conditions specified on the column in table to maintain data integrity within the database?

Making sure only valid data is stored in the database is called as data integrity.

Constraints in sql server are classified into

1 declarative integrity constraint

2 procedural integrity constraint

1 procedural integrity constraint

Are the constraint that cannot be specified while creating the table and can be specified only by using triggers?

Declarative integrity constraint

Are the constraint that can be specified while creating the table?

Declarative integrity constraint can be specified either at column level or table level.

Note:

 When we have to specify a declarative integrity on a single column then it can specified either at column level or table level, but when you have to specify declarative integrity constraint on combinations of  multiple columns then you must specify at table level.

Declarative Integrity Constraint

Not Null

When we insert rows into the table without providing values for one or more columns in a table then the values in those columns is called as NULL

When we don’t want to allow Null into a columns then specify Not Null constraint on that column.

Not null constraint can be specified only on a single column and it cannot be specified on combination of multiple columns hence it can be specified only on single column, and it cannot be specified at table level.

Syntax : <Column Name> data type Not Null

It allows duplicate values.

Default: Is used to provide default value for a column that will be automatically inserted into the column when user insert a row into that table without providing a value for that column.

DEFAULT constraint can be specified only on single column and it cannot be specified on combination of multiple columns and hence it can be specified only at column level and it cannot be specified at table level.

Syntax:

<Column name> <data type>

Default <default value>    

Unique:

Is used for not allowing duplicate values into a column

Note:

When we specify unique constraint on a column or combination of columns then an index will be automatically created on that column or combination of columns which is by default “Non Clustered Index”

Super key:

A column or combination of any columns in a table that can be uniquely identify the rows in a table is called as super key.

 If we take a table with the name departments with the columns deptid, deptname, and city as example

Then the super keys available in that table are as follows:

Deptid

Deptname

Deptid, deptname

Deptid, city

Deptname, city

Deptid, deptname, city

Candidate key

Among the available super keys with minimum no of columns is called as candidate key.

In the above example of department table there are two candidate keys

Deptid,

Deptname

Primary Key:

A column declared within the primary key doesn’t allow duplicate and Null Values.

When we want to use a column or combination of columns in a table to uniquely identify the rows then specify the primary key constraint on that column or combination of column.

Syntax:

<Colname> <data type>

[Constraint <constraint name>]

Primary key

Table level

[Constraint <constraint name>

Primary key (<column name>)

On primary key column also an index is automatically created, which is by default “clustered Index”

Foreign Key or Referential Integrity Constraint

Is used to restrict the user from inserting only those values that are available in a referenced column

A column to be referred by a foreign key must have primary key.

Foreign key allows Nulls and duplicates values.


Rules

Delete Rules

It specifies how child record is effected if the parent record is deleted

On delete no action

On delete cascade

On delete set null

On delete set default

On delete no action

If foreign key is declared without any delete rule , then parent record can’t be deleted if

along with child records

matching child records exists

On delete cascade

If foreign key is declared with  on delete cascade, if we delete the parent record the parent is deleted

On delete set null

If foreign key is declared with on delete  set null, then if you delete the parent record , parent record is deleted without deleting the child records, but the child record foreign key value is set to Null

On delete set Default

If foreign key is declared with on delete set Default. If parent record is deleted then child record foreign key value set to default value available on foreign key column

Update Rules

On update no action

Parent cant be updated , if matching child records exists

On update Cascade

If the foreign key is declared with on update cascade, then if the primary key is updated matching foreign key also updated automatically

On update set null

The foreign key declared with on update set null, then if the primary key is updated the matching foreign key value set to null

On update set Default

If primary key is updated so matching foreign key value set to default value

To specify Default Constraint In GUI

To specify Check Constraint