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 student12(no number(2) default 11,name varchar(2));

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

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

    

     SQL> select * from student12;

        NO   NAME

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

         1             a

        11            b

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

      SQL> select * from student12;

        NO   NAME

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

         1             a

        11            b

                     C

-- Default can not override nulls.

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

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 student12(no number(2) , name varchar(10), marks number(3) check

             (marks > 300));

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

             constraint ch  check(marks > 300));

      TABLE LEVEL

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

             (marks > 300));

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

             constraint ch check(marks > 300));

      ALTER LEVEL

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

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

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 student12 enable constraint un;

DISABLE

This will disable the constraint.

Ex:

     SQL> alter table student12 disable 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 student12 enforce constraint un;

DROP

This will remove the constraint.

Ex:

     SQL> alter table student12 drop constraint un;

     Once the table is dropped, constraints automatically will drop.