Constraints

Constraints

These are used to impose restrictions on a table.

These are automatically activated whenever the DML statements are performed on the table.

This will provide the High Security.

Domain Integrity Constraints:

These  are used to check for conditional restrictions of columns on tables….

1)Not Null: It is used for conditional restriction for a column compulsory…

EX: Create table xx_std_cons (Sno  Number NOT NULL

                                             ,sname varchar2(100)

                                                     ,age number);

è Table Created…

Now let us try to insert some records in the above table…

Insert  into xx_std_cons values(1,’A’,20);

Insert  into xx_std_cons values(2,’B’,20);

Insert  into xx_std_cons values(‘’,’C’,20);

This above insert statement errors bcz the not null constraint is putten for the column sno.

It should not be null….

Check Constarint: It is used to force the user to enter the avlues in a given range which will be checked during the insertion.

EX: Create table xx_std_cons1 (Sno  Number NOT NULL

                                             ,sname varchar2(100)

                                                     ,age number

         ,sex char(1) check(sex in(‘M’,’F’)));

Insert  into xx_std_cons1 values(1,’A’,20,’M’);

Insert  into xx_std_cons1 values(2,’B’,20,’’);

Insert  into xx_std_cons1 values(2,’B’,20,’P’);

This above insert statement errors bcz the Check constraint is putten for the column sex.

The value  should be wither M or F or NULL.

Entity Integrity Constraints:

1)Unique: It is used to force the user to enter the Unique value into the column on a table…

                  The Unique Constraints can be null.

                 

EX: Create table xx_std_cons2 (Sno  Number UNIQUE

                                             ,sname varchar2(100)

                                                     ,age number

         ,sex char(1) check(sex in(‘M’,’F’)));

Insert  into xx_std_cons2 values(1,’A’,20,’M’);

Insert  into xx_std_cons1 values(2,’B’,20,’’);

Insert  into xx_std_cons1 values(2,’C’,20,’F’);

This above insert statement errors bcz the Unique constraint is putten for the column Sno.

The value  should be Unique or NULL.

EX: Create table xx_std_cons3 (Sno  Number UNIQUE NOT NULL

                                             ,sname varchar2(100)

                                                     ,age number

         ,sex char(1) check(sex in(‘M’,’F’)));

Now as per above example the sno can be not null and also unique….

Primary Key: It acts as  a marker to the record on a table.

It cannot be null and must be unique and a index will be created  for the column on which the primary    key is placed.

Primary Key := Unique+Not Null+Index.

Note: A table can only have one Primary key. But the primary can be placed on a single column or combination of more then one column…

EX: Create table xx_std_cons3 (Sno  Number PRIMARY KEY

                                             ,sname varchar2(100)

                                                     ,age number

         ,sex char(1) check(sex in(‘M’,’F’)));

References:

Columns level references: This references will be applied for maintaining the relation ship between master and child level.The column level references are used when we place the primary key on a single column.

Master level table Primary key will be equal to child level table foreign key.

Master Table: Create table xx_dept(Deptno Number Primary Key

                                                                ,Dname varchar2(100) Not Null

                                                               ,loc         varchar2(100));

Insert into xx_dept values(10,’Accounting’,’New York’);

Insert into xx_dept values(20,’Sales’,’Florida’);

Child Level Table:  Create table xx_emp(Empno Number Primary Key

                                                            ,Ename varchar2(100) Not Null

                                                            ,Sex char(1) check(sex in (‘M’,’F’))

                                                            ,deptno Number referenced xx_dept(deptno));

Insert into xx_emp values(101,’A’,’M’,10);

Insert into xx_emp values(102,’B’,’M’,20);

Insert into xx_emp values(103,’C’,’F’,10);

Insert into xx_emp values(104,’D’,’M’,30);

The above insert stmt fails bcz the constraint is violated as parent key not found,this is as deptno 30 doesnot exists in the master table…

Table level references: This references will be applied for maintaining the relationship between master and child level. The table level references are used when we place the primary key on more than one column.

Master level table Primary key will be equal to child level table foreign key.

EX:   Create table xx_bank_dtls(accno Number

                                                ,acctype char(1)

                                                ,accname varchar2(100) Not Null

                                                ,Sex char(1) check(sex in (‘M’,’F’))

                                                ,Constraint pk_k1 primary key(accno,acctype)

                                                ,Constraint chk_type check(acctype in (‘S’,’C’)));

Insert into xx_bank_dtls values(100,’S’,’A’,’M’);    

Insert into xx_bank_dtls values(100,’C’,’A’,’M’);    

Insert into xx_bank_dtls values(101,’S’,’B’,’M’);    

Insert into xx_bank_dtls values(101,’C’,’B’,’M’);    

Insert into xx_bank_dtls values(100,’S’,’C’,’M’); 

The above insert stmt errors bcz the combination of accno and acctype should be unique as the primary key is placed on the combination of those  2columns….

Child table:  

 Create table xx_trxn_dtls(accno number

                                     ,acctype char(1)

                                    ,trx_type char(1)

                                    ,amount number

                                    ,Constraint fl_trxn foreing key(accno,acctype) references xx_bank_dtls(accno,acctype));

Inser t into xx_trxn_dtls values(100,’S’,’D’,1000);

Inser t into xx_trxn_dtls values(100,’S’,’W’,3000);

Inser t into xx_trxn_dtls values(101,’S’,’D’,1000);

Inser t into xx_trxn_dtls values(101,’S’,’W’,1000);

Inser t into xx_trxn_dtls values(103,’S’,’D’,1000);

The above insert stmt fails bcz the constraint is violated as parent key not found,this is as acctno 103 and acttype S  combination doesnot exists in the master table…

On Delete Cascade(Limportant for interview)

When ever we give the relation ship between master and child table and we would like to  delete any record from the master table it will not allow us to delete that record if any records exists for that master record in the child table. Inorder to delete the record from the master table we need to delete all the records related to it in the child table then we will be able to delete that master record.

Now By using on delete cascade we can delete the master record even if the records exists in the child table for that master ecord.

It will delete the master record and as well the related records n the child table.

EX:

   Create table xx_bank_dtls(accno Number

                                                ,acctype char(1)

                                                ,accname varchar2(100) Not Null

                                                ,Sex char(1) check(sex in (‘M’,’F’))

                                                ,Constraint pk_k1 primary key(accno,acctype)

                                                ,Constraint chk_type check(acctype in (‘S’,’C’)));

Insert into xx_bank_dtls values(100,’S’,’A’,’M’);    

Insert into xx_bank_dtls values(100,’C’,’A’,’M’);    

Insert into xx_bank_dtls values(101,’S’,’B’,’M’);    

Insert into xx_bank_dtls values(101,’C’,’B’,’M’);    

Insert into xx_bank_dtls values(102,’S’,’G’,’M’);    

Insert into xx_bank_dtls values(102,’C’,’G’,’M’);

Not Null	Unique	Primary Key	Foreign Key
Null Values are not allowed	Null Values are allowed	Null Values are not allowed	Null Values are  allowed
Duplicate Values allowed	Duplicate Values not allowed	Duplicate Values not allowed	Duplicate Values allowed

Exercise:

1)      Create Item_master table with below colums

 Item_code – primary key

 Item description

 Item name not null

Create item child table with below columns

 Item code –reference to the item code of the master table

 Price –check>10000

 Quanity – check>0

2)      Create Medicine master table

Medicine code

Medicine no

n  Create primary key for the above combination of both columns

Medicine type

Child table

Medicine code

Medicine no

n   Create foreign key for the above combination of both columns with master table

             Price not null

             Qty not null (qty >0)

Interview questions:

1)What is Primary Key and foreign Key and what is difference.

2)Difference between not null,unique and primary key.

3)ow can we delete the parent record even though the child records exists for that parent record(primary key).

Child table:  

 Create table xx_trxn_dtls(accno number

                                     ,acctype char(1)

                                    ,trx_type char(1)

                                    ,amount number

                                    ,Constraint fl_trxn foreing key(accno,acctype) references xx_bank_dtls(accno,acctype)

                                              ON DELETE CASCADE);

Inser t into xx_trxn_dtls values(100,’S’,’D’,1000);

Inser t into xx_trxn_dtls values(100,’S’,’W’,3000);

Inser t into xx_trxn_dtls values(101,’S’,’D’,1000);

Inser t into xx_trxn_dtls values(101,’S’,’W’,1000);

Inser t into xx_trxn_dtls values(102,’S’,’D’,1000);

Inser t into xx_trxn_dtls values(102,’S’,’W’,3000);

Inser t into xx_trxn_dtls values(102,’S’,’D’,4000);

           

Now run the below delete command----

Delete from xx_bank_dtls where accno = 101;

Commit;

This will delete all the records form the xx_bank_dtls table for the acctno is 101 and as well all the records form the xx_trxn_dtls table where acctno is 101 as we have placed on delete cascade.

INDEX:

Normal Index: If we create the index on a single column then it is called as normal index.

Create table xx_std_inx(sno number primary key

                                     ,sname varchar2(100)

,sex char(1)));

Create index inx_1 on xx_std_inx(sname);

Composite Index: If we create the index on more then one column then it is called as composite  index.

Create table xx_std_inx1(sno number primary key

                                     ,std_first_name varchar2(100)

                                    , ,std_last_name varchar2(100)

,sex char(1)));

Create index inx_i_1 on xx_std_inx1(std_first_name, std_last_name);

UNIQUE Normal Index: If we create the index on a single column with unique keyword

 then it is called as normal index.

Create table xx_std_inx2(sno number primary key

                                     ,sname varchar2(100)

,sex char(1)));

Create UNIQUE index inx_2 on xx_std_inx(sname);

Composite Index: If we create the index on more then one column then it is called as composite  index.

Create table xx_std_inx3(sno number primary key

                                     ,std_first_name varchar2(100)

                                    , ,std_last_name varchar2(100)

,sex char(1)));

Create UNIQUE  index inx_i_3 on xx_std_inx1(std_first_name, std_last_name);