SQL Step by Step (part-3)

CONSTRAINTS

Constraints are categorized as follows.

Domain integrity constraints

ü  Not null

ü  Check

Entity integrity constraints

ü  Unique

ü  Primary key

Referential integrity constraints

ü  Foreign key

Constraints are always attached to a column not a table.

We can add constraints in three ways.

ü  Column level -- along with the column definition

ü  Table level                 -- after the table definition

ü  Alter level                  -- using alter command

While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.

If you want to give a name to the constraint, you have to use the constraint clause.

NOT NULL

This is used to avoid null values.

We can add this constraint in column level only.

Ex:

     SQL> create table student(no number(2) not null, name varchar(10), marks

             number(3));

     SQL> create table student(no number(2) constraint nn not null, name varchar(10),

             marks number(3));

CHECK

This is used to insert the values based on specified condition.

We can add this constraint in all three levels.

Ex:

     COLUMN LEVEL

     SQL> create table student(no number(2) , name varchar(10), marks number(3) check

             (marks > 300));

      SQL> create table student(no number(2) , name varchar(10), marks number(3)

             constraint ch  check(marks > 300));

      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3), check

             (marks > 300));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint ch check(marks > 300));

      ALTER LEVEL

      SQL> alter table student add check(marks>300);

      SQL> alter table student add constraint ch check(marks>300);

UNIQUE

This is used to avoid duplicates but it allow nulls.

We can add this constraint in all three levels.

Ex:

      COLUMN LEVEL

     SQL> create table student(no number(2) unique, name varchar(10), marks

             number(3));

      SQL> create table student(no number(2)  constraint un unique, name varchar(10),

             marks number(3));

       TABLE LEVEL

     

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             unique(no));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint un unique(no));

      ALTER LEVEL

      SQL> alter table student add unique(no);

      SQL> alter table student add constraint un unique(no);

PRIMARY KEY

Ø  This is used to avoid duplicates and nulls. This will work as combination of unique and not null.

Ø  Primary key always attached to the parent table.

Ø  We can add this constraint in all three levels.

Ex:

      COLUMN LEVEL

      SQL> create table student(no number(2) primary key, name varchar(10), marks

             number(3));

      SQL> create table student(no number(2)  constraint pk primary key, name varchar(10),

             marks number(3));

     

      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             primary key(no));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint pk primary key(no));

      ALTER LEVEL

      SQL> alter table student add primary key(no);

      SQL> alter table student add constraint pk primary key(no);

FOREIGN KEY

Ø  This is used to reference the parent table primary key column which allows duplicates.

Ø  Foreign key always attached to the child table.

Ø  We can add this constraint in table and alter levels only.

Ex:

      TABLE LEVEL

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             primary key(empno), foreign key(deptno) references dept(deptno));

      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             constraint pk primary key(empno), constraint fk foreign key(deptno) references

             dept(deptno));

      ALTER LEVEL

    

      SQL> alter table emp add foreign key(deptno) references dept(deptno);

                SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno);

Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.

       

USING ON DELTE CASCADE

By using this clause you can remove the parent record even it childs exists.

Because when ever you remove parent record oracle automatically removes all its dependent records from child table, if this clause is present while creating foreign key constraint.

Ex:

      TABLE LEVEL

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             primary key(empno), foreign key(deptno) references dept(deptno) on delete

             cascade);

      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             constraint pk primary key(empno), constraint fk foreign key(deptno) references

             dept(deptno) on delete cascade);

     

      ALTER LEVEL

      SQL> alter table emp add foreign key(deptno) references dept(deptno) on delete

             cascade;

                SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno) on

                     delete cascade;

COMPOSITE KEYS

A composite key can be defined on a combination of columns.

We can define composite keys on entity integrity and referential integrity constraints.

Composite key can be defined in table and alter levels only.

Ex:

      UNIQUE (TABLE LEVEL)

     

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             unique(no,name));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint un unique(no,name));

           

      UNIQUE (ALTER LEVEL)

      SQL> alter table student add unique(no,name);

      SQL> alter table student add constraint un unique(no,name);

     PRIMARY KEY (TABLE LEVEL)

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             primary key(no,name));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint pk primary key(no,name));

      PRIMARY KEY (ALTER LEVEL)

      SQL> alter table student add primary key(no,anme);

      SQL> alter table student add constraint pk primary key(no,name);

      FOREIGN KEY (TABLE LEVEL)

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             dname varchar(10), primary key(empno), foreign key(deptno,dname) references

             dept(deptno,dname));

      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             dname varchar(10), constraint pk primary key(empno), constraint fk foreign

             key(deptno,dname) references dept(deptno,dname));

      FOREIGN KEY (ALTER LEVEL)

    

      SQL> alter table emp add foreign key(deptno,dname) references dept(deptno,dname);

                SQL> alter table emp add constraint fk foreign key(deptno,dname) references

                     dept(deptno,dname);

DEFERRABLE CONSTRAINTS

Each constraint has two additional attributes to support deferred checking of constraints.

Ø  Deferred initially immediate

Ø  Deferred initially deferred

Deferred initially immediate checks for constraint violation at the time of insert.

Deferred initially deferred checks for constraint violation at the time of commit.

Ex:

     SQL> create table student(no number(2), name varchar(10), marks number(3),

             constraint un unique(no) deferred initially immediate);

     SQL> create table student(no number(2), name varchar(10), marks number(3),

             constraint un unique(no) deferred initially deferred);

     SQL> alter table student add constraint un unique(no) deferrable initially deferred;

   

      SQL> set constraints all immediate;

     This will enable all the constraints violations at the time of inserting.

      SQL> set constraints all deferred;

     This will enable all the constraints violations at the time of commit.

OPERATIONS WITH CONSTRAINTS

Possible operations with constraints as follows.

Ø  Enable

Ø  Disable

Ø  Enforce

Ø  Drop

ENABLE

This will enable the constraint. Before enable, the constraint will check the existing data.

Ex:

     SQL> alter table student enable constraint un;

DISABLE

This will disable the constraint.

Ex:

     SQL> alter table student enable constraint un;

ENFORCE

This will enforce the constraint rather than enable for future inserts or updates.

This will not check for existing data while enforcing data.

Ex:

     SQL> alter table student enforce constraint un;

DROP

This will remove the constraint.

Ex:

     SQL> alter table student drop constraint un;

     Once the table is dropped, constraints automatically will drop.

CASE AND DEFAULT

CASE

Case is similar to decode but easier to understand while going through coding

Ex:

SQL> Select sal,

          Case sal

                    When 500 then ‘low’

                    When 5000 then ‘high’

                    Else ‘medium’

          End case

          From emp;

       SAL          CASE

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

       500          low

      2500         medium

      2000         medium

      3500         medium

      3000         medium

      5000         high

      4000         medium

      5000         high

      1800         medium

      1200         medium

      2000         medium

      2700         medium

      2200         medium

      3200         medium

DEFAULT

Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.

When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.

Ex:

     SQL> create table student(no number(2) default 11,name varchar(2));

     SQL> insert into student values(1,'a');

     SQL> insert into student(name) values('b');

    

     SQL> select * from student;

        NO   NAME

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

         1             a

        11            b

       SQL> insert into student values(null, ‘c’);

      SQL> select * from student;

        NO   NAME

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

         1             a

        11            b

                     C

-- Default can not override nulls.

  

ABSTRACT DATA TYPES

Some times you may want type which holds all types of data including numbers, chars and special characters something like this. You can not achieve this using pre-defined types.

You can define custom types which holds your desired data.

Ex:

     Suppose in a table we have address column which holds hno and city information.

     We will define a custom type which holds both numeric as well as char data.

   

     CREATING ADT

     SQL> create type addr as object(hno number(3),city varchar(10)); /

    

     CREATING TABLE BASED ON ADT

     SQL> create table student(no number(2),name varchar(2),address addr);

    

     INSERTING DATA INTO ADT TABLES

     SQL> insert into student values(1,'a',addr(111,'hyd'));

     SQL> insert into student values(2,'b',addr(222,'bang'));

     SQL> insert into student values(3,'c',addr(333,'delhi'));

    

     SELECTING DATA FROM ADT TABLES

      SQL> select * from student;

        NO NAME ADDRESS(HNO, CITY)

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

         1        a     ADDR(111, 'hyd')

         2        b     ADDR(222, 'bang')

         3        c     ADDR(333, 'delhi')

      SQL> select no,name,s.address.hno,s.address.city from student s;

        NO NAME  ADDRESS.HNO ADDRESS.CITY

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

         1             a          111                 hyd

         2             b          222                 bang

         3             c          333                 delhi

UPDATE WITH ADT TABLES

SQL> update student s set s.address.city = 'bombay' where s.address.hno = 333;

SQL> select no,name,s.address.hno,s.address.city from student s;

        NO NAME  ADDRESS.HNO ADDRESS.CITY

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

         1             a          111                 hyd

         2             b          222                 bang

         3             c          333                 bombay

DELETE WITH ADT TABLES

SQL> delete student s where s.address.hno = 111;

SQL> select no,name,s.address.hno,s.address.city from student s;

        NO NAME  ADDRESS.HNO ADDRESS.CITY

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

         2             b          222                 bang

         3             c          333                 bombay

DROPPING ADT

SQL> drop type addr;

OBJECT VIEWS AND METHODS

OBJECT VIEWS

If you want to implement objects with the existing table, object views come into picture.

You define the object and create a view which relates this object to the existing table nothing but object view.

Object views are used to relate the user defined objects to the existing table.

Ex:

     1) Assume that the table student has already been created with the following columns

          SQL> create table student(no number(2),name varchar(10),hno number(3),city

                  varchar(10));

     2) Create the following types

          SQL> create type addr as object(hno number(2),city varchar(10));/

          SQL> create type stud as object(name varchar(10),address addr);/

     3) Relate the objects to the student table by creating the object view

          SQL> create view student_ov(no,stud_info) as select no,stud(name,addr(hno,city))

                  from student;

     4) Now you can insert data into student table in two ways

          a) By regular insert

               SQL> Insert into student values(1,’sudha’,111,’hyd’);

          b) By using object view

               SQL> Insert into student_ov values(1,stud(‘sudha’,addr(111,’hyd’)));

METHODS

You can define methods which are nothing but functions in types and apply in the tables which holds the types;

Ex:

    1) Defining methods in types

         SQL> Create type stud as object(name varchar(10),marks number(3),

                 Member function makrs_f(marks in number) return number,

                   Pragma restrict_references(marks_f,wnds,rnds,wnps,fnps));/

     2) Defining type body

          SQL> Create type body stud as

                  Member function marks_f(marks in number) return number is

                  Begin

                     Return (marks+100);

                  End marks_f;

                  End;/

      3) Create a table using stud type

           SQL> Create table student(no number(2),info stud);

      4) Insert some data into student table

           SQL> Insert into student values(1,stud(‘sudha’,100));

      5) Using method in select

           SQL> Select s.info.marks_f(s.info.marks) from student s;

      -- Here we are using the pragma restrict_references to avoid the writes to the      

          Database.

VARRAYS AND NESTED TABLES

VARRAYS

A varying array allows you to store repeating attributes of a record in a single row but with limit.

Ex:

    1) We can create varrays using oracle types as well as user defined types.

         a) Varray using pre-defined types

              SQL> Create type va as varray(5) of varchar(10);/

         b) Varrays using user defined types

              SQL> Create type addr as object(hno number(3),city varchar(10));/

              SQL> Create type va as varray(5) of addr;/

    2) Using varray in table

         SQL> Create table student(no number(2),name varchar(10),address va);

    3) Inserting values into varray table

         SQL> Insert into student values(1,’sudha’,va(addr(111,’hyd’)));

         SQL> Insert into student values(2,’jagan’,va(addr(111,’hyd’),addr(222,’bang’)));

    4) Selecting data from varray table

         SQL> Select * from student;

         -- This will display varray column data along with varray and adt;

         SQL> Select no,name, s.* from student s1, table(s1.address) s;

         -- This will display in general format

    5) Instead of s.* you can specify the columns in varray

         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

    -- Update and delete not possible in varrays.

    -- Here we used table function which will take the varray column as input for producing

        output excluding varray and types.

      

     NESTED TABLES

A nested table is, as its name implies, a table within a table. In this case it is a table that is represented as a column within another table.

Nested table has the same effect of varrays but has no limit.

Ex:

    1) We can create nested tables using oracle types and user defined types which has no

         limit.

         a) Nested tables using pre-defined types

              SQL> Create type nt as table of varchar(10);/

         b) Nested tables using user defined types

              SQL> Create type addr as object(hno number(3),city varchar(10));/

              SQL> Create type nt as table of addr;/

    2) Using nested table in table

         SQL> Create table student(no number(2),name varchar(10),address nt) nested table 

                  address store as student_temp;

    3) Inserting values into table which has nested table

         SQL> Insert into student values (1,’sudha’,nt(addr(111,’hyd’)));

         SQL> Insert into student values (2,’jagan’,nt(addr(111,’hyd’),addr(222,’bang’)));

    4) Selecting data from table which has nested table

         SQL> Select * from student;

         -- This will display nested table column data along with nested table and adt;

         SQL> Select no,name, s.* from student s1, table(s1.address) s;

         -- This will display in general format

    5) Instead of s.* you can specify the columns in nested table

         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

    6) Inserting nested table data to the existing row

         SQL> Insert into table(select address from student where no=1)

                  values(addr(555,’chennai’));

    7) Update in nested tables

         SQL> Update table(select address from student where no=2) s set s.city=’bombay’

                 where s.hno = 222;

    8) Delete in nested table

         SQL> Delete table(select address from student where no=3) s where s.hno=333;

DATA MODEL

Ø  ALL_COLL_TYPES          

Ø  ALL_TYPES               

Ø  DBA_COLL_TYPES          

Ø  DBA_TYPES               

Ø  USER_COLL_TYPES         

Ø  USER_TYPES              

FLASHBACK QUERY

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

Ø  Time base flashback

Ø  SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

     a) SQL> Select *from student;

          -- This will display all the rows

     b) SQL> Delete student;

     c) SQL> Commit;              -- this will commit the work.

     d) SQL> Select *from student;

         -- Here it will display nothing

     e) Then execute the following procedures

         SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)

     f) SQL> Select *from student;

         -- Here it will display the lost data

         -- The lost data will come but the current system time was used

     g) SQL> Exec dbms_flashback.disable

          -- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

     a) Declare a variable to store SCN

          SQL> Variable s number

     b) Get the SCN

          SQL> Exec :s := exec dbms_flashback.get_system_change_number

     c) To see the SCN

         SQL> Print s

     d) Then execute the following procedures

          SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

          SQL> Exec dbms_flashback.disable

EXTERNAL TABLES

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location with in oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Ex:

CREATING DIRECTORY AND OS LEVEL FILE

    SQL> Sqlplus system/manager

    SQL> Create directory saketh_dir as ‘/Visdb/visdb/9.2.0/external’;

     SQL> Grant all on directory saketh_dir to saketh;

     SQL> Conn saketh/saketh

     SQL> Spool dept.lst

     SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from dept;

     SQL> Spool off

CREATING EXTERNAL TABLE

 SQL> Create table dept_ext

         (deptno number(2),

         Dname varchar(14),

         Loc varchar(13))

         Organization external  ( type oracle_loader

                                                 Default directory saketh_dir

                                                 Access parameters

                                                 ( records delimited by newline

                                                    Fields terminated by “,”

                                                    ( deptno number(2),

                                                      Dname varchar(14),

                                                      Loc varchar(13)))

         Location (‘/Visdb/visdb/9.2.0/dept.lst’));

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)   You can not perform insert, update, and delete operations

a)   Indexing not possible

b)   Constraints not possible

BENEFITS OF EXTERNAL TABLES

a)   Queries of external tables complete very quickly even though a full table scan id required with each access

b)   You can join external tables to each other or to standard tables