Data Constraints or Integrity Constraints

 

Integrity constraints or Data constraints

Types

1.NOT NULL

2.UNIQUE

3.PRIMARY KEY

4.DEFAULT

5.CHECK

6.FORIEGN KEY OR REFERENCIAL INTEGRITY

1.NOT NULL: - Data must enter and duplicate values allowed

Ø  Create table student (Sno number constraint Sno_NN NOT NULL);

2.UNIQUE: -no duplicate value allowed and null value allowed

Column level

Ø  Create table student (Sno number constraint Sno_UNQ UNIQUE);

Table level

Ø  Create table student (Sno number, Name varchar2(100), constraint Sno_NM_UNQ UNIQUE (Sno, Name));

3.PRIMARY KEY: -It will not accept null, duplicate values. Only one Primary key allowed on one table

Column level

Ø  Create table student (Sno number constraint Sno_PK PRIMARY KEY);

Table level

Ø  Create table student (Sno number, Name varchar2(100), constraint Sno_NM_PK PRIMARY KEY (Sno, Name));

4.DEFAULT: -

Ø  Create table student (Sno number, J_Date date constraint JOD_DF DEFAULT sysdate);

 

5.CHECK: -

Ø  Create table student (Sno number constraint Sno_PK PRIMARY KEY constraint Sno_chk CHECK (Sno between 10 and 50), Location varchar2(50) constraint loc_def DEFAULT ‘HYDERABAD’ constraint loc_chk CHECK Location in(‘HYDERABAD’,’GUNTUR’,’VIJAYAWADA’));

6.FORIEGN KEY: -

ON DELETE CASCADE: -Remove child table record automatically when parent table record is removed

ON DELETE SET NULL: -When parent table record is removed then child table record values as set null

Ø  Create table emp (deptno number constraint dn_fk references dept(deptno) ON DELETE CASCADE);

ADDING CONSTRAINT TO EXISTING TABLE

NOT NULL, DEFAULT constraints can be added by using alter …. Modify clause

Remaining all constraints can be added by using alter …. Add clause

Ø  Alter table student add constraint Sno_pk PRIMARY KEY (Sno));

Ø  Alter table student modify Sname constraint snm_nn NOT NULL; 

Note:- cascade option in the drop clause is used to drop any of the dependent constraints if available

DROPPING CONSTRAINT

Ø  Alter table student drop constraint Sno_pk CASCADE;

Ø  Alter table student drop PRIMARY KEY;

Ø  Alter table student drop UNIQUE (Sno);

DISABLE CONSTRAINT

Ø  Alter table student disable constraint Sno_pk CASCADE;

ENABLE CONSTRAINT

Ø  Alter table student enable constraint Sno_pk;

TO CHECK CONSTRAINT ON A TABLE

Ø  Select * from user_constraints;

Ø  Select * from user_constraints where table_name=’student’;

Ø  Select * from user_cons_columns where table_name=’student’;

       Please watch and subscribe my Channel

        Kamal SQL CLASSES in Telugu