Integrity constraints

Integrity constraints

·         Constraint means rule.

·         We can define a restriction to the  column in a table is called constraint.

·         Integrity Constraints are database objects.

·         Integrity Constraints are used to full fill the business rules in an organization.

·         Integrity Constraints are built in functions.

Types of  integrity  Constraints: 

                1.Domain Integrity Constraints

o   NOT  NULL

o   CHECK

o   DEFAULT

  2.Entity Integrity Constraints

o   UNIQUE

o   PRIMARY  KEY

    

  3.Referential Integrity Constraints

o   FOREIGN KEY

            Composite Integrity  constraints:  The constraints are which are used in  more than one column called composite integrity constraints  .they are

         1.composite unique constraint: The unique which is used in more than one columns in a table called  composite unique constraint.

         2. composite primary key: The primary key which is used in more than one column is called composite primary key.

         3.composite foreign key: The foreign key which is used in more than one column is called composite foreign key.

THE  COMPOSITE  CONSTRAINTS  ARE  USED   IN TABLE LEVEL ONLY.

The constraints are used in two levels.

   1.column level: The constraints which are used in columns

         Syntax: (col name  <data type>  [constraint connname] conntype);

Here conname is optional ,if u r not giving  connname oracle automatically takes  connname.

2. table level:  constraints are defined in at the time creating the table or after creation of the table.

 Syntax1. Create table < tabname> (col1 dt,col2 dt…….)[constraint conname] conntype(col1,col2………);

 Syntax2.alter table add [constraint conname] contype(col1,col2………);

 The following are some of the integrity constraints in oracle

·         NOT NULL

·         UNIQUE

·         PRIMARY KEY

·         FOREIGN KEY

·         CHECK

·         DEFAULT

1.NOT NULL:-

v  Not null is used to suppress the null values.

v  It is allow the duplicates.

v  Not null is used only in column level.

EX: SQL> create table branchmstr(brno varchar2(20) constraint brno_nn not null,brname varchar2(20));

Table created.

SQL> insert into  branchmstr values('null','sr nagar');

1 row created.          //here null is treated as string

SQL> insert into  branchmstr values(100,'sr nagar');

1 row created.

SQL> insert into  branchmstr values(100,'sr nagar');

1 row created.           //not null allows duplicates.

SQL> insert into  branchmstr values(null,'sr nagar');

insert into  branchmstr values(null,'sr nagar')

                               *

ERROR at line 1:     //not null suppres  explisict null values

ORA-01400: cannot insert NULL into ("VARA"."BRANCHMSTR"."BRNO")

SQL> insert into  branchmstr(brname) values('sr nagar');

insert into  branchmstr(brname) values('sr nagar')

*

ERROR at line 1:        //not null suppres  implsict null values.

ORA-01400: cannot insert NULL into ("VARA"."BRANCHMSTR"."BRNO")

SQL> select * from branchmstr;

BRNO                 BRNAME

-------------------- --------------------

null                 sr nagar

100                  sr nagar

100                  sr nagar

2. UNIQUE :-   

v  It is used to suppress  duplicate values.

v  It will allow null values.

v  One table can have more than one unique key.

v  Index is automatically provided by UNIQUE constraint.

v  It is used in more than one column called composite unique constraint.

v  A table can have more than one composite unique constraints.

UNIQUE at column level:

EX:

SQL> create table branchmstr(brno varchar2(10) constraint brno_unq unique,brname  varchar2(20) constraint  brname_nn  not null);

Table created.

SQL> insert into branchmstr values(null,'srnagar');

1 row created.

SQL> insert into branchmstr values(null,'srnagar');

1 row created.          //one null is not equal to another null

SQL> insert into branchmstr values('null','srnagar');

1 row created.
SQL> insert into branchmstr values('Null','srnagar');

1 row created.

SQL> insert into branchmstr values('100','srnagar');

1 row created.

SQL> insert into branchmstr values('100','srnagar');

insert into branchmstr values('100','srnagar')

*

ERROR at line 1:           //unique not allow duplicates.

ORA-00001: unique constraint (VARA.BRNO_UNQ) violated

SQL> select * from branchmstr;

BRNO       BRNAME

---------- --------------------

           srnagar

           srnagar

null       srnagar

Null       srnagar

100        srnagar

UNIQUE at table level:

Ex: SQL> create table productmstr(prono varchar2(10),proname varchar2(10),qtyonhand

number(8), constraint prono_unq unique(prono));

Table created.    //here is we use unique in column level also because constraint is defined  on one clumn only.

SQL> create table fzspdnote(fzno varchar2(10),spno varchar2(10),dod date,empno n

umber(8),constraint fzspno_unq unique(fzno,spno));

Table created.

SQL> insert into fzspdnote values('fz101','sp101',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz101','sp102',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz102','sp102',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz102','sp101',sysdate,100);

1 row created.

SQL> insert into fzspdnote(spno,dod,empno) values('sp101',sysdate,100);

1 row created.

SQL> select * from fzspdnote;

FZNO       SPNO       DOD            EMPNO

---------- ---------- --------- ----------

fz101      sp101      26-SEP-13        100

fz101      sp102      26-SEP-13        100

fz102      sp102      26-SEP-13        100

fz102      sp101      26-SEP-13        100

                sp101           26-SEP-13             100

In above table there is no frezer but spare part is deliver so there is no meaning.

So we can introduce new constraint called primary key.

             

      

3.PRIMARY KEY:-

v  It is an entity integrity constraint.

v  It does not allow null values and duplicate values.

v  A primary key combine both not null and unique behavior in single declaration

v  One table can have only one primary key /one composite primary key.


   It will provide index automatically.

PRIMARY KEY at column level:-

EX: SQL> create table incr(incrno number(4) constraint incrno_pk primary key,incrdat

e date not null,incramt number(8,2) not null);

Table created.

SQL> insert into incr values(100,sysdate,1000);

1 row created.

SQL> insert into incr values(100,sysdate,1000);

insert into incr values(100,sysdate,1000)

*

ERROR at line 1:            //pk does not allow duplicate values.

ORA-00001: unique constraint (VARA.INCRNO_PK) violated

SQL> insert into incr values(200,sysdate,1000);

1 row created.

SQL> insert into incr values(null,sysdate,1000);

insert into incr values(null,sysdate,1000)

                        *

ERROR at line 1:                 // pk does not allow null values

ORA-01400: cannot insert NULL into ("VARA"."INCR"."INCRNO")

SQL> insert into incr(incrdate,incramt) values(sysdate,1000);

insert into incr(incrdate,incramt) values(sysdate,1000)

*

ERROR at line 1: :                       // pk does not allow null values

ORA-01400: cannot insert NULL into ("VARA"."INCR"."INCRNO").

SQL> select * from incr;

INCRNO INCRDATE     INCRAMT

---------- --------- ----------

       100 26-SEP-13       1000

       200 26-SEP-13       1000

PRIMARY KEY at table level:-

EX:SQL> create table fzspdnote(fzno varchar2(10),spno varchar2(10),dod date,empno number(8),constraint fzspno_cpk primary key(fzno,spno));

Table created.

SQL> insert into fzspdnote values('fz101','sp101',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz101','sp102',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz102','sp102',sysdate,100);

1 row created.

SQL> insert into fzspdnote values('fz102','sp101',sysdate,100);

1 row created.

SQL> insert into fzspdnote(spno,dod,empno) values('sp101',sysdate,100);

insert into fzspdnote(spno,dod,empno) values('sp101',sysdate,100)

*

ERROR at line 1:                      //pk does not allow null values.

ORA-01400: cannot insert NULL into ("VARA"."FZSPDNOTE"."FZNO")

4.FOREIGN KEY:-

v   

It will represent relationship between tables.

v  It will allow null  values ,duplicate values.

v  We can create a relationship between two tables by using references.

v  The foreign key can have relationship with other/same table primary key or unique key only.

v  The primary key value and foreign key  values are may or may not be same but the data type of two columns are must be same.

v  We can provide relationship between composite primary key and composite foreign key.

v  One or more number of foreign key in a table .

v  ON DELETE CLAUSE:-

          1.ON DELETE CASCADE: By using this whenever the parent date will be removed then automatically child date will be also deleted.

         2.ON DELETE SET NULL:  By using this whenever the parent data will be removed then automatically child data will become null where is the parent data  as reference. 

EX:

SQL> create table dept1(deptno number(6) constraint deptno_pk primary key,

  dname varchar2(10) constraint dname_nn not null,loc varchar2(10) constraint

 loc_nn not null);

Table created.

DEPTNO DNAME      LOC

---------- ---------- ----------

       101 ACC        NEW YARK

       102 SALES      CHICAGO

       103 MARKETING  BOSTON

SQL> create table emp1(empno number(8) constraint empno_pk primary key,ename var

char2(10),job varchar2(10) constraint job_nn not null,mgr number(8) constraint mgr_fk references emp1(empno) on delete set null,hiredate date constraint date_nn not null,sal number(8,2),deptno number constraint deptno_fk references dept1(de

ptno) on delete cascade);

Table created.

EMPNO ENAME      JOB               MGR HIREDATE         SAL     DEPTNO

---------- ---------- ---------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK            7902 27-SEP-13        800        102

      7856 KING       MGR                        27-SEP-13       5000        101

      7566 FORD       ANA              7856 27-SEP-13       3000        102

      7902 JONES      SM               7566 27-SEP-13       4000        103

SQL> delete from dept1 where deptno=101;

1 row deleted.

SQL> select * from dept1;

DEPTNO DNAME      LOC

---------- ---------- ----------

       102 SALES      CHICAGO

       103 MARKETING  BOSTON

SQL> select * from emp1;

     EMPNO ENAME      JOB               MGR HIREDATE         SAL     DEPTNO

---------- ---------- ---------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK            7902 27-SEP-13        800        102

      7566 FORD       ANA                          27-SEP-13       3000        102

      7902 JONES      SM               7566   27-SEP-13       4000        103

SQL> delete from emp1 where empno=7566;

1 row deleted.

SQL> select * from emp1;

   EMPNO ENAME      JOB               MGR HIREDATE         SAL     DEPTNO

---------- ---------- ---------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK            7902 27-SEP-13        800        102

      7902 JONES      SM                           27-SEP-13       4000        103

WORKING WITH COMPOSITE PRIMARY KEY:

SQL> create table fz_srv_item(fzno varchar2(10) constraint fzno_pk primary key,

    fztype varchar2(10) constraint fztype_nn not null,

rdate date constraint rdate_nn not null);

Table created.

FZNO       FZTYPE     RDATE

---------- ---------- ---------

FZ101      12S        13-JAN-13

FZ102      6S         13-JAN-13

FZ103      12S        12-JAN-13

SQL> create table fz_sp_item(fzspno varchar2(10) constraint fzspno_pk primary key,

fzsptype varchar2(20) constraint fzsptype_nn not null,

rdate date constraint rsdate_nn not null);

Table created.

SQL> create table fz_sp_dnote(fznoref varchar2(10) constraint fzspref_fk references fz_srv_item(fzno),

fzspnoref varchar2(10) constraint fzsptyperef_fk references fz_sp_item(fzspno),

ddate date,

constraint ref_cpk primary key(fznoref,fzspnoref));

Table created.

FZNOREF    FZSPNOREF  DDATE

---------- ---------- ---------

FZ101      SP101      17-JAN-13

FZ101      SP102      17-JAN-13

FZ102      SP102      17-JAN-13

FZ102      SP101      17-JAN-13

FZ103      SP101      17-JAN-13

WE CAN CREATE COL LEVEL CONSTRAINTS AND TABLE LEVEL CONSTRAINT AT TIME[ IN SINGLE TABLE].

5.CHECK constraint:-

It defines conditions that each row must satisfy.

A single column can have multiple check constraints.

The check constraints can be defined at col level and also at table level.

Restrictions:-Iin below cases  check constraint  shows error.

     1.Queries to refer to values in other rows.

     EX: create table emp(deptno constraint deptno_chk check(deptno in(select deptno from dept));

 2.reference to the cuuval,nestval,level or rownum

EX: create table dept(deptno number(8),dname varchar2(20),loc varchar2(10), constraint deptno_chk check(deptno=rownum));

3.calls to sysdate,uid,usrenv……

EX: crate table emp(hiredate date check(hiredate=sysdate);

EX:
          SQL> create table dept(deptno number(2) constraint dno_pk1 primary key constrain

t dno_chk1 check(deptno between 10 and 90),dname varchar2(20) constraint dname_n

n1 not null constraint dname_chk1 check(dname=upper(dname)),loc varchar2(20) def

ault 'NEWYARK' constraint loc_chk1 check(loc in('NEWYARK','DALLAS','BOSTON','CHI

CAGO')));

Table created.

SQL> insert into dept values(10,'ACC',null);

1 row created.

SQL> insert into dept values(40,'ACC',default);

1 row created.

SQL> insert into dept(deptno,dname) values(20,'SALES');

1 row created.

DEPTNO DNAME                LOC

---------- -------------------- --------------------

        10 ACC

        40 ACC                  NEWYARK

        20 SALES                NEWYARK

6.DEFAULT constraint:-

If value is not provided for table column default will be taken.

The default value must be a literal or expression or sql function.

The datetype of  default value must be match with data type of the column.

ADDING CONSTRAINT TO A TABLE:-

We can add a constraint to the table after creation of the table by using alter with add clause.

Not null,defaultconstraints are added to the table alter with modify clause.

SYNTAX: alter table< tabname >add [constraint con_name] con_type(col1,[col2………]);

EX:

·         Alter table emp add constraint empno_pk primary key(empno);

·         Alter table emp add constraint emp_mgr_fk foreifn key(mgr) references emp(empno);

·         Alter table emp modify empno number(8) constraint empno_nn not null;

·         Alter table emp modify hiredate date default sysdate;

DROPPING constraints :-

·         To drop a constraint identify the conname the user_constraints and user_cons_columns data dictionary.

·         To drop a constraint using alter with drop clause.

·         The cascade option is used to drop any dependent constraints.

SYNTAX:

Alter table <tabname> drop primary key/unique(colname) constraint conn_name [cascade];

EX:

   Alter table empdrop primary key;

  Alter table dept drop unique (dname);

 WHEN DROP THE PRIMARY KEY OR UNQUE THE INDEX WILL BE AUTIMATICALLY WILL BE DELETED.

DISABLING CONSTRAINTS:-

The constraints can be disable with out dropping it or recreating it.

The alter with disable clause is used to disable the constraints.

The cascade option will disable dependent constraints.

SYNTAX: alter  table <tabname> disable constraint<constraint_name>[cascade];

EX:alter table emp disable constraint empno_pk cascade;

ENABLING CONSTRAINTS:-

The alter with enable clause is used to enable the constraints.

SYNTAX:
alter table <tabname> enable constraint<conn_name];

EX:

Alter table emp constraint mgr_fk;

When the primary key or unique is enabled the index will automatically created.