oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Default Values and Managing Constraints in Oracle

DEFAULT

You can also specify the DEFAULT value for columns i.e. when user does not enter anything in that column then that column will have the default value. For example in EMP table suppose most of the employees are from Hyderabad, then you can put this as default value for CITY column. Then while inserting records if user doesn’t enter anything in the CITY column then the city column will have Hyderabad.

To define default value for columns create the table as given below

create table emp (empno number(5),
                   name varchar2(20),
                   sal number(10,2),
                   city varchar2(20) default ‘Hyd’);

Now, when user inserts record like this

insert into emp values (101,’Sami’,2000,’Bom’);

Then the city column will have value ‘Bom ‘. But when user inserts a record like this

insert into emp (empno,name,sal) values (102,’Ashi’,4000);

Then the city column will have  value ‘Hyd’. Since it is the default.

Examples

Defining Constraints in CREATE TABLE statement.

create table emp (empno number(5) constraint emppk
                                  Primary key,
                   ename varchar2(20) constraint namenn
                                  not null,
                   sal  number(10,2) constraint salcheck
                        check (sal between 1000 and 20000)
                   idno varchar2(20) constraint id_unique
                             unique );

create table attendance (empno number(5) constraint empfk
                             references emp (empno)
                             on delete cascade,
                   month varchar2(10),
                   days number(2) constraint dayscheck
                             check (days <= 31) );

The name of the constraints are optional. If you don’t define the names then oracle generates the names randomly like ‘SYS_C1234’

Another way of defining constraint in CREATE TABLE statement.

create table emp (empno number(5),
                   ename varchar2(20) not null,
                   sal  number(10,2),
                   idno varchar2(20),
                    constraint emppk Primary key (empno)
             constraint salcheck check (sal between 1000 and 20000)
            constraint id_unique unique (idno) );

create table attendance (empno number(5),
                   month varchar2(10),
                   days number(2),
constraint empfk foreign key (empno)    
     references emp (empno)
       on delete cascade
     constraint dayscheck
           check (days <= 31) );

 

Deferring Constraint Checks

You may wish to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of the following characteristics:

When dealing with bulk data being manipulated by outside applications, you can defer checking constraints for validity until the end of a transaction.

Ensure Constraints Are Created Deferrable

After you have identified and selected the appropriate tables, make sure their FOREIGN, UNIQUE and PRIMARY key constraints are created deferrable. You can do so by issuing a statement similar to the following:

create table attendance (empno number(5),
   month varchar2(10),
    days number(2),
     constraint empfk foreign key (empno)
       references emp (empno)
         on delete cascade  
          DEFERRABLE
           constraint dayscheck
            check (days <= 31) );

Now give the following statement

set constraint empfk deferred;
update attendance set empno=104 where empno=102;
insert into emp values (104,’Sami’,4000,’A123’);
commit;

You can check for constraint violations before committing by issuing the SET CONSTRAINTS ALL IMMEDIATE statement just before issuing the COMMIT. If there are any problems with a constraint, this statement will fail and the constraint causing the error will be identified. If you commit while constraints are violated, the transaction will be rolled back and you will receive an error message.

 

ENABLING AND DISABLING CONSTRIANTS

You can enable and disable constraints at any time.

To enable and disable constraints the syntax is

ALTER TABLE <TABLE_NAME> ENABLE/DISABLE
           CONSTRAINT  <CONSTRAINT_NAME>

For example to disable primary key of EMP table give the following statement

alter table emp disable constraint emppk;

And to enable it again, give the following statement

alter table emp enable constraint emppk;

 

Dropping constraints

You can drop constraint by using ALTER TABLE DROP constraint statement.

For example to drop Unique constraint from emp table, give the following statement

alter table emp drop constraint id_unique;

To drop primary key constraint from emp table.

alter table emp drop constraint emppk;

The above statement will succeed only if the foreign key is first dropped otherwise you have to first drop the foreign key and then drop the primary key. If you want to drop primary key along with the foreign key in one statement then CASCADE CONSTRAINT statement like this

alter table emp drop constraint emppk cascade;

 

Viewing Information about constraints

To see information about constraints, you can query the following data dictionary tables.

select * from user_constraints;
select * from user_cons_columns;

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved