Constraints in SQL Server

CONSTRAINTS

                                                                                                               

Constraints are used to prevent / stop invalid data entry into our tables. Constraints are  created on table columns only.

SQL Server supports the following constraint types

* Domain Integrity Constraints

                                * NOT NULL

                                * CHECK

                                * DEFAULT

* Entity Integrity Constraint

                                * UNIQUE

                                * PRIMARY KEY

* Referential Integrity constraints

                                * FOREIGN KEY

                               

All the above constraints can be defined at 2 levels

                1. Column Level Constraints

                2. Table Level Constraints

Column Level Constraints :

Here we are defining constraints on individual columns i.e first we are defining column

Immediately we are specifying constraint type

Syntax: 

CREATE TABLE <T.N>(CN1 DT(S) CONSTRAINT TYPE,CN2 DT(S) CONSTRAINT TYPE,-----,CNn CONSTRAINT TYPE)

Table Level Constraints:

Here we are defining constraints on group of columns i.e first we are defining columns and lost

 Only specify the constraint type along with columns

Syntax: 

CREATE TABLE <T.N>(CN1 DT(S),CN2 DT(S)...... CONSTRAINT TYPE(CN1,CN2))

Note:

This method is also is useful to add constraints on existing table existing columns.

Not Null:

It won't accept NULL values but it will accept duplicate values. When a column contains NOT NULL then those columns are mandatory columns i.e user must pass values.

Note: NOT NULL constraint does not support table level

Whenever we are copying a table from another table not null constraint automatically copied

Syntax to create NOT NULL on table Column Level

CREATE TABLE <T.N>(<C.N> DT(SIZE) CONSTRAINT_TYPE,CN2 DT(SIZE))

CREATE TABLE [NOTNULL](ID INT NOT NULL,NAME VARCHAR(20))

Unique:

It supports both table level and column level and also it won't accept duplicate values but it

Will accept one NULL value. when ever if you create a unique key on columns in the table SQLSERVER automatically creates non-clustered index key so its performance is high.

Column Level:

CREATE TABLE UNQ(ID INT UNIQUE, NAME VARCHAR(40))

Table Level:

CREATE TABLE UNQT(SNO INT,SNAME VARCHAR(20),UNIQUE(SNO,SNAME))

Primary Key:

Primary Key uniquely identifies a row in a table. One table should contain only one primary key

in a table and also it won't accept duplicate, null values. Whenever primary key is created on columns in the table SQL Server automatically creates clustered index key so its performance is very high.

Note:

Composite P.K can be placed on max 16 columns.

Column Level:

CREATE TABLE PKC(ID INT PRIMARY KEY,NAME VARCHAR(10) UNIQUE NOT NULL)

Table Level :

CREATE TABLE PKT(ID INT,NAME VARCHAR(10),PRIMARY KEY(ID,NAME))

Note: 

Combination of columns as a primary key is called as composite primary key

Foreign Key:

If you want establish relationship between tables then we should use referential integrity constraints

One table foreign key must belongs to another table primary key . Foreign key accept duplicate , null values . Always foreign key values based on primary key values only.

Column-Level:

CREATE TABLE <T.N>(CN1 DT(S) REFERENCES <PKT.N>(COLUMN-NAME))

CREATE TABLE FKC(ID INT REFERENCES PKC(ID))

TABLE-LEVEL:

CREATE TABLE FKT(ID INT,NAME VARCHAR(10) FOREIGN KEY(ID,NAME) REFERENCES PKT)

Note:

The table which contains primary key on columns in the table is called as Parent table or master

Table.

the table which contains foreign key on columns in the table is called as child table or secondary

table

if you are trying to update or delete records in child table sqlserver violates an error to avoid this error

we have to add 2 clauses.

ON DELETE CASCADE:

                If parent row is deleted from parent table then child rows wil be deleted from child

 table

Ex: delete from emp where deptno=10

ON UPDATE CASCADE:

                If parent row is updated in parent table then corresponding child rows will be updated in

 Child table.

CHECK CONSTRAINT:

                Used to define logical conditions according to our business rules.

COLUMN_LEVEL:

CREATE TABLE CKC(ID INT,SAL INT CHECK(SAL>4000))

TABLE-LEVEL:

CREATE TABLE CKT(ID INT,SAL INT,BONUS INT,CHECK(SAL>4000 AND BONUS>2000))

WAQ to define a check constraint on a name column to insert only capital letters

CREATE TABLE TESTCHECK(NAME VARCHAR(10) CHECK(NAME=UPPER(NAME)))

Assigning user defined names to constraints:

In our SQLSERVER we can also create our own name using constraint keyword. These names are

Called constraint names.

Syntax: 

Constraint user defined name constraint type

Ex:          constraint pk_id               primary key

Adding constraints on existing table

1. If you want to add constraints on existing table existing columns then we are using table

level syntax

                create table test(id int)

                ex: alter table test add constraint pk_id primary key

Note: 

The column should contain not null constraint before applying primary column

2. If you want to add a new column along with constraints we are using column level syntax

                Ex: alter table test add name varchar (10) unique

To add not null

Alter table <t.n> alter column <c.n> dt(s) not null

To add foreign key:

Alter table testext add foreign key(id) references test.

Constraint Management

* To display all constraints present in a table

                sp_helpconstraint <tname>

                sp_helpconstraint withdrawls

* To disable or enable or drop constraint

                Alter table <tname> nocheck/check/drop constraint <consName>

Ex:

                Alter table products nocheck constraint UNQ_PID

Note:    We cannot disable or enable P.K and Unique key constraints.