Different Integrity Constraints in SQL Server

  1. UNIQUE
  2. NOT NULL
  3. PRIMERY KEY
  4. CHECK
  5. FOREIGN KEY

Integrity Constraints:

  1. Entity Integrity (Unique, Primary Key)
  2. Domain Integrity (Not Null, Check)
  3. Referential Integrity (Foreign Key)

(i) unique key integrity constraints:-

  • Unique does not allow duplicate values.

Example  :    empno                    int                   unique

                           100

                           101

                           100

Note: it allows null values

(ii) not null key integrity constraints     :-

  • A column declared with not null into that column null values are not allowed.

Example  : Ename varchar (20) not null.

Note : it allows duplicate values

(iii) primary key integrity constraints   :

  • A column declared with primery key doesn’t allow duplicate and null values.
  • A column declared with primery key,uniquely identifies each record in a table.
  • A table allows only one primery key.
  • Numeric columns are preferred as primery keys then character columns.

(iv) check integrity constraints sql:-

  • Check constraints validate data based on a condition.

Example  :- sal amallmoney check (sal>3000)

Sal smallmoney check(sal between 1000 and 5000)

(v) Forign Key integrity constraints sql  :

  • A foreign key refers primery key(or)unique columns of another/same table.
  • It is used to establish relationship between two tables,.

Rule :– The values of foreign key should be match with primery key value. (or) foreign can be null.

How to declare the foreign key :-

Dro int references dept(dno)
  • After declaring foreign key a relationship is established between two tables and that relationship is called parent/child relationship(or)master/detail relationship.
  • Table holding primary key is “parent” and table holding foreign key is “child” -> (referring).

Constraints can be declared in two ways-

  1. Column level
  2. Table level

(i) COLUMN LEVEL  :-

  • Column level constraints are declared immediately declaring column.
  • Use column level if only column participating constraints declaration.

Syntax   :-

Create table <tabname>

(colname datatype(size)

[constraint<name>]type,

Colname data type(size)

[constraint <name>]type,

——————————-

)

Example :- dept22 table

  • Create table dept22 (dno int primery key, dname varchar(20) unique not null,loc varchar (15))
  • Insert into dept22 values (10,’sales’,’hys’)

Example  :- emp22 table:-> parent

  • Create table emp22 (eno int constraint pk_emp22 primery key, Ename varchar(20) not null, Sal smallmoney constraintck_sal_emp22 check(sal>3000), Dno int references dept22(dno))
  • Insert into emp22 values (1,’a’,5000,10)

1) By default sql server creates one to many relationship.
2) To create one to one relationship declare forign key with unique constraint.