Category: Tutorials

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.

INTEGRITY CONSTRAINTS    :-     

• Business rules can be implemented in two ways  :

1. Declarative Approach
2. Procedural Approach

• Declarative using integrity constraints
• Procedural using database triggers.
• If business rules are simple,then they com be implemented by using Integrity constraints.
• If business rules are complex, then the database-triggers.
• Integrity constraints used to : implement the business rule and Used to validate data.
• An integrity constraint ensures data integrity.

Integrity constraints are 3 types  :-

1. Entity integrity
2. Domain integrity
3. Referential integrity

IDENTITY:–    

• It is used to generate serial numbers for a column in a table mostly for a primary keys.

Syntax :- identity (seed,incr)

                       Seed ->Starting value.

                        Incr -> increment by value

• Both seed,incr are optional.
• If you don’t mention the seed ,incr then the default value is (1,1).

Example :- Create table cust

(cid int identity(100,1),

Cname varchar (20))

Insert into cust (cname)

Values (‘a’)

Select * from cust

     cid              cname

    100                  a

    101                  b

    102                  c

DDL COMMANDS :-

• Create
• Alter
• Drop
• Truncate.

Create Command in SQL Server

Creating a table from another table  :-

             Syntax :-  

                Select <collist> into <New tabname>

                From <old tabname>

                 [where <cond>]

Example:–     Select * into <new emp10 from emp.

{all stracture  &data is copyed from emp table to new table.}

Coping particular columns &particular records :

                  Select empno ,ename,sal

                  Into emp 11

Coping only structure but not data :

                   Select * into emp12

                   From emp where     1=2  {Here we can give  any false condition}

Coping only the data but not structure  :-

• It is not possible.

(2) Alter Command in SQL Server :-

• Alter command is used to modify structure of a table.
• Use alter command to do the following things  :

       1. Adding new column

       2. Droping column

       3. Modifying column ->[incr | DECR field size] -> [changing data type]

(i ) Adding new columns  :-   

Syntax : Alter table <tabname>

                  Add colname data type [size]

Example:   Alter table emp11

                       Add job varchar(20)

(ii)Drpping column  :  

Syntax  :         Alter table <tablename>

                           Drop column name.

Example:–  Alter table emp11

                          Drop column job.

{Note  :  A table is there atleast one column must there}

(iii)Modifying column   :    

Synatx : alter table <tabname>

                  Alter column colname data type(size)

Example:  Alter table emp

                      Alter column ename varchar(20)

Note: Decrement is possible up to max. length

Sp_help<tabneame>

Example: sp_help  emp.

• This command show structure of table.
• Sp stands for stored procedure.

Note: — To change a data type of the column,column must be empty.

Drop Command in SQL Server   :-

• Drop command is used to drop the table from the database.

Syntax   :  drop table<tabname>

Example: drop table EMP.

TRUNCATE Command in SQL Server :-

• To delete all records from table (or) makes table empty.

Syntax : Truncate table <tabname>

Example:   truncate table emp11.

Differences between Delete command and Truncate Command:

DELETE Command

(i) Delete command deletes all or particular Records

(ii) Delete will not release memory.

(iii) In delete, restoring is possible.

(iv) Delete will not reset identity

 TRUNCATE  Command

(i)  Truncate deletes only all records.

 (ii) Truncate releases memory.

 (iii) Truncate restoring is not possible.

 (iv)  Truncate will reset identity.

  DML COMMANDS :-

1. Insert
2. Update
3. Delete
4. Merge

Insert Command in SQL Example:

Copying the data from one table to another table:–

(i)   Syntax: insert into <target table>

Select * from <source table>

Note:   Both target table & source table structure must be same.

Example: insert into emp1

Select * from emp       {coping all records to emp1}

(2) copying the particular record :

 Example: insert into emp1

Select * from emp where deptno in(10,20)

(3) If structure is different :     

{emp1 having (Target Tab) 3 cols emp

Emp having  ( Source Tab) number of cols}

Insert into emp1

Select empno,ename,sal from emp.

(4). If emp1 table having : Tar. Table

 Empno ename sal dno hiredate

And emp table having : source table

Empno ename sal.

Example :insert into emp1(empno,ename,sal)

Select *from emp.

UPDATE COMMAND (Update Command in SQL Example) :–

• Update command is used to modify data in the table.

Syntax   :-

                    Update <tabname>

                     Set column name=value

                      [colname=value——–]

                      [where <condition>]

• update all employees commission is to 500.

Update employee set comm=500

• Update the employee commission is 500,whos commission is  null,”

      Update emp set comm=500

        Where comm is NULL.

• ”Increment employee commission by 200 whos commission is not null”.

      Update emp set comm =comm+200

             Where comm is not null.

• Increment employee salary by 10% those who are working for dept 10&20.

                 Update emp set sal=sal*1.1

                  Comm =comm*1.2

    Where dept in (10,20).

• Increment employee salary as follows:

     If clerk         10%

Salesman            15%

Manager              20%

Others                   5%

Update emp

         Set sal=case job

                      When ‘clerk’ then sal*1.1

                       When ‘salesman’ then sal*1.15

                        When ‘manager’ then sal*1.2 

                     Else

                         Sal *1.05

                            End

DELETE COMMAND (Delete Command in SQL Example) :–

• It is used to delete record(or)records from a table.

Syntax  :- delete from <tabname>[where <condition>]

Example:- delete from emp where empno=7369.

Delete from emp where ename like ‘s%’

ROW  FRAGMENT    :-

• Row fragmentation occurs when varchar fields are updated.
• The value belongs to one row stored in two different rows.
• Row fragmentation degrates performance.
• The fields which are not frequently updated there fields must be declared with varchar type.
• The fields which are frequently updated those fields must be declared with char type.

CASE STATEMENT :-

Case statement works like if then else.
It is a ANSII standard.
Case statements are two types  :

1. Simple case
2. Searched case

1) sample case statement in sql server   :–

• Syntax  :

Case      expression

When value1 then return expression1

When value2 then return expression2

——————————–

———————————

Else

      Return expewssion

   End

Example  :   Select  ename, Case job

                        When ‘clerk’ then ‘worker’

                       When ‘manager’ then ‘boss”

                       When ‘president’ then ‘big boss’

                Else

                         ‘Employee’

                         End as designation

                          From emp

Ename                    Designation

Smith                       worker

Arun                         boss

Chitty                         bigboss

Venky                         employee

B                                    employee

2) Searched case statement in sql server:-

• Syntax  :

                Case

                 When cond1 then return expr1

                  When   cond2    then   return exp2

                       ———————

                       Else

                    Return expr

                     End

Difference: 

• If condition is based on equal to operation then use simple case.
• If condition is based on other than equal operation  {like [<,>,<=,>=]} then use “searched case”.

Example: — Select ename,sal,

                      Case

                    When sal>3000 then  ‘highsal’

                       When sal<3000 then ‘lowsal’

                        Else

                       ‘Moderate sal’

                         End as sal range

                         From emp.

Example: student table

               Sno        sname         s1        s2        s3

               If  in eed like this

             Sname    stot       savg       result.

Select sname ,(s1+s2+s3) as stot

                        (s1+s2+s3)/3) as savg,

        Case

        When s1>=35 and sz>=35 and

                   S3>=35 then ‘pass’

Else

     ‘fail’

End as result

  From student

DISTINCT CLAUSE:

• The main purpose of using this distinct clause is to eliminates duplicates.

Syntax of  Distinct Clause : distinct <column>/<collist>

Example:  Select distinct job from emp

         Job

     Analyst

      Clerk

       Manager

       Salesman

Example:- Select distinct deptno, job from emp

Deptno                    job

10                           manager

10                           clerk

20                            manager

20                             hr

20                             clerk

Order by clause  :-

• It is used to sort data return by the select statement based on one (or) more columns.

• Order by clause sort data in descending order (or) In ascending order.

Syntax :

                Select <collist>from<tabname> [ where<cond>] Order by <collist>[asc\desc]

                        [Note : default in ascending order]

Sorting the emptable by salary desc.

Example  :-  select * from emp order by sal

Order by sal desc wise

•     Select * from emp Order by sal desc.

Sorting the emptable by ename wise   :–

•    Select  * from emp Order by ename (or) {we can give the column name (or) column number} 
• Select * from emp Order by 6

( The number is based select list not a table)

Example  :    select empno,ename, sal from emp Order by 3

Example  : Select empno,ename,sal*12 anusal From emp Order by anusal.

{Note   : Column alias are can be used in order by clause but not Used in where clause}

Arrange employee records department wise within the department salary wise

•          Select deptno,ename,sal,job from emp Order by dept no,sal desc

Example-       deptno                 ename                sal                   job

                              10                           A                  500                clerk

                               10                           B                  400              clerk

                                20                           C                  600            manager

                                 20                           B                   300           manager

                                  30                           D                   200          manager  

Display employee records working for 10 on 20^th departments and sort the results salary wise descending order.

•                  Select * from emp Where deptno in (10,20)  Order by sal desc.

Like Operator in Sql Server Example:-

• It is used to compare column value with character patterns.

Syntax: Like’pattern’

                 Not like ‘pattern’.

• The pattern consists of    a-z , 0-9,  
• Metacharacters -> %  (this is to replace none (or) meta characters).
• _ [Under score] -> to replace exactly one character
• [  ]   -> to replace range (or) set of characters

Display employee records name starts with ‘S’

• Select * from emp   Where ename like’S%’

Name ends with ‘S’

• Select * from emp Where ename like ‘%S’.

Name starts with ‘S’& ends with ‘S’

• Select * from emp  Where ename like ‘S%S’

If ‘S’ contain any place:

• Select * from emp Where ename like ‘%S%’.

Where ‘a’ is the second character in the name

• Select * from emp  Where  ename like’-a’

Display employee records name starts between a and p.

• Select * from emp  Where ename ,like ‘[a-p]%’

Where name doesn’t start with a and p

• Select * from emp Where ename like ‘[^ a-p]%’  (or)  Not like ‘[a-p]%’.

Display employee records names starts with

       A (or)  b (or) s (or) t

•    Select *from emp  Where ename like’[abst]%’
•    Select * from emp  Where ename like ‘[^abst]%’

Escape Operator in Sql Server  :-

Display the _names

•      Select * from emp Where ename like ‘%\_%’
•    Escape ‘\’.

Use escape option the string contains metacharacter.

Is Operator in Sql Server   :-

• Used to compare column value with null (or)not null

Syntax :

                Is null

                Is not null

”Display emplist whose commission is NULL”

• Select * from emp Where comm. Is null
• Select * from emp Where comm. Is not null.