Constraints are used to maintain the database in integrity state. Below are some of the constraints available in SQL Sevrer:

Identity Constraint:

Creates an identity column in the table.

It is used with CREATE TABLE and ALTER TABLE.

Syntax:

IDENTITY(SEED,INCREMENT)

Example:

CREATE TABLE emp(empno INT IDENTITY(2,1), ename VARCHAR(20)

Default it is IDENTITY(1,1)

Only one identity column is possible for a table. By default it is incremented by 1, we can also specify our own for increment. At the time of insertion we should not give values for the identity column.

Example:

INSERT emp VALUES(‘suneetha’).

By using the above statement a record will be inserted into the emp table by having 1 for empno column, and the next record will be incremented by 1. We cannot update identity column. Identity column is not reset when a record is deleted. We cannot give an explicit value for the identity column. For inserting an explicity value in the identity column, we can do as follows:

Example:

SET IDENTITY_INSERT emp ON

INSERT INTO EMP VALUES(25,’abc’)

SET IDENTITY_INSERT emp OFF

The next value will be the next highest value. If we insert any record by using the above statement, the next record starts from 26 (as 25 is the last record and we have given to increment a record by 1, the next values starts from 26). Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. An identity cannot be given to the existing column.

Primary Key Constraint:

This is mainly used for identifying a unique record in the table. The column that is defined with the primary key constraint cannot have null values and duplicate values. No two rows can have same primary key value. A table can have only one primary key. A single column having a primary key can be given at column level or at table level. If we create a PK without giving a constraint name then default name is give to the constraint.

Example:

CREATE TABLE emp(empno INT [CONSTRAINT ] PRIMARY KEY, ename VARCHAR(20))

We can have combination of columns as a primary key. If combination of columns is taken as a primary key, then they should be declared at table level.

Example:

CREATE TABLE emp(empno INT , ename VARCHAR(20),mgr_id int,PRIMARY KEY(empno,mgr_id))

We can add a primary key to the existing table. To add a primary key to the existing table make sure that the existing column is a not null column. If the existing table contains a null value then first alter that column with not null then add a primary key to the corresponding column. This can be done as follows, assume emp table has empno column which is null column, now first we have to make it as not null column and then add primary key.

Example:

ALTER TABLE emp ALTER COLUMN empno INT not null

ALTER TABLE emp ADD [CONSTRAINT ] PRIMARY KEY (empno)

If we want to add a primary key to the existing table which has null value previously now we can do by making it as not null by using check constraint can be done as follows:

With nocheck constraint can be applied only with foreign key and check constraints.

Example:

ALTER TABLE emp WITH NOCHECK ADD CONSTRAINT FOREIGN KEY(deptno) REFRENCES dept(deptno)

Unique Key Constraint:

It enforces the uniqueness of the values in a set of columns. A column with UNIQUE key allows only one NULL value and no duplicates value. No two rows can have the same value for the columns. Primary key also enforces uniqueness but do not support NULL values.

Below are the differences between Primay Key and Unique Key Constraints:

Both Primary and Unique keys are used to identify the rows/tuple/entry of the table uniquely.

Primary Key	Unique Key
Only one Primary Key is possible for a table.	Multiple Unique Keys are possible for a table.
Primary Key column doesn’t allows NULL Values	Unique Key column allows one NULL Value
Whenever a Primary Key is created on a table, then by default a Clustered Index is created on that table. Hence there can be only one Clustered Index for a table.	Whenever a Unique Key is created on a table, then by default a Non – Clustered Index is created on that table. Hence there can be upto 249 Non Clustered Indexes for a table.When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

Foreign Key Constraint:

 It identifies and enforce the relationship between tables. A foreign key in one table points to a candidate key in another table. For example, in the following table, the emp table establishes a foreign key that references the dept table.

Example:

CREATE TABLE dept(deptno INT PRIMARY KEY)

CREATE TABLE emp(empno INT PRIMARY KEY, deptno FOREIN KEY REFERENCES dept(deptno))

Master table must contain a Primary Key. The column for which we are referring should have same datatype as in the master table. We cannot delete a record in the emp table if it has a value that is referring in the master table.

Example:

In Dept table: 10

In Emp Table: 1 10

Here, value 10 in emp table is referring to the value in the dept table, hence we cannot delete this column in the dept table. We cannot truncate master table if we have foreign key, even though when there are no records are in the child table. We cannot update master table if that corresponding values are present in the child table. Inorder to update or delete values for the master child tables we can use on delete cascade and on update cascade.

Cascading:

 Cascade should be given to the child table. If cascade is given in the child table, then whatever changes made to the master table will also updated or deleted in the child table. We can do on delete cascade and on update cascade. Using cascade we can delete or update the records in the master table. We cannot alter child table by giving references when the data is already present.

Example:

CREATE TABLE emp (empno INT PRIMARY KEY, deptno INT FOREIGN KEY REFERENCES dept(deptno) on delete cascade on update cascade)

Default:

 Default constraint enforces default values in the column of the table. The system defined default value for nullable column in NULL. We can give our own default values for the columns. Because nullable columns may not be desirable, a better solution could be to define a DEFAULT definition for the column. When a value is inserted into the column with the DEFAULT definition for a column, we implicitly instruct the SQL Server to insert a default value in the column when a value is not specified for it. If a column is a NOT NULL column and if a DEFAULT definition is inot defined then database Engine returns an error that states that the column does not allow null values. We can create a default definition as a part of the table definition when we create a table.

Syntax:

CREATE TABLE [table] (DEFAULT)

Example:

CREATE TABLE emp(date INT DEFAULT getdate())

We can also add DEFAULT for an existing table. Each column may or may not contain DEFAULT values. When a DEFAULT definition is added to an existing table, by default the server applies the default values to new rows of data that are added to the table. Existing data will not be affected by this new DEFAULT definition.

Syntax:

ALTER TABLE [table] ADD CONSTRAINT DEFAULT FOR [ColName]

Example:

ALTER TABLE emp ADD CONSTRAINT def_sal DEFAULT 100 FOR hra

We can modify or delete an existing DEFAULT definition.

Syntax:

ALTER TABLE [table] DROP CONSTRAINT [ConstraintName]

Example:

ALTER TABLE emp DROP CONSTRAINT def_sal

Default definitions cannot be created on columns defined with the following:

1. A timestamp data type.

2. For an IDENTITY property.

3. For an existing DEFAULT definition.

Check Constraint:

 It enforces domain integrity by limiting the values that are accepted by a column. For example, the range of

the salary column can be limited by creating a CHECK constraint that allows for only data that ranges from 15000-100000. This prevents entering the values of the salary beyond the range. We can create a CHECK constraint to a single column or combination of columns. For single column, it can be created at column level or table level. For combination of columns it has to be created at table level. CHECK constraint reject values that evaluate to FALSE. Database server allows NULL values for a CHECK constraint column as the NULL value evaluates to UNKNOWN. We can create a CHECK constraint at the time of table creation.

Syntax:

CREATE TABLE [table] ( CHECK( [or IN()))

Example:

CREATE TABLE emp (sal INT check(sal>15000 and sal<100000))

CREATE TABLE emp(gender CHAR CHECK (gender IN(‘m’,’f’)))

We can also create a CHECK constraint to an existing table. There can be multiple check constraints for a column and a table.

Syntax:

ALTER TABLE [table] ADD CONSTRAINT [ConstraintName] CHECK([Check Constraint])

Example:

ALTER TABLE emp ADD CONSTRAINT hra_ck CHECK(hra=100)