Different Integrity Constraints in SQL Server

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.