1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

default in oracle

Discussion in 'SQL PL/SQL' started by monkey, Jun 3, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I wrote:

    create table dog(
    dogID integer,
    dname varchar2(10),
    dschool varchar2(10) default 'ss',
    );

    The whole table was empty.
    When I tried to insert values, I got value for school the one I wrote in insert into statement, so I never got 'ss' for school.
    Please, is it possible at all to do in oracle and how?
    http://www.w3schools.com/sql/sql_default.asp
    Many thanks!!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Creating a table in this manner always creates an empty table. Data needs to be inserted before a table will be populated. There are several ways to do this:


    insert into ... select ... from ...;
    insert into ... (column list) values (value list);
    insert all
    into ... values (value list)
    into ... values (value list)
    ...
    select * From dual;


    You can also use Create Table ... As Select ... (seen as CTAS on the web) to both create and populate a table:


    create table emp2 (empno number, ename varchar2(20), sal number, job varchar2(10), deptno number)
    as select empno, ename, sal, job, deptno from emp;
     
    monkey likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi "monkey",

    David has nicely covered all the various ways to insert into a table.

    I feel you have one more concern when I read:

    The default value which you defined as constraint on column "dschool" will only be populated with "ss" if you do NOT specify any value for the column in your INSERT statement.

    Try, for example:


    Code (SQL):
    SQL> INSERT INTO dog(dogID, dname)  VALUES (1, 'Labrador');

    1 ROW created.

    SQL> SELECT * FROM dog;

         DOGID DNAME      DSCHOOL
    ---------- ---------- ----------
         1 Labrador   ss

    P.S: There's an extra "," on the last line of your CREATE statement - suppose it's just copy/paste error !
     
    monkey likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Correct and I missed that declaration. DEFAULT values only populate when the column value is NOT supplied by the insert statement; if you were to supply NULL as a value the resulting record would record that NULL and not assign the default:


    Code (SQL):

    SQL> INSERT INTO dog VALUES(2,'Fleabag',NULL)
      2  /


    1 ROW created.


    SQL> SELECT * FROM dog;


         DOGID DNAME      DSCHOOL
    ---------- ---------- ----------
             1 Bowser     ss
             2 Fleabag


    SQL>
     

    Simply because the column value is NULL doesn't mean it gets the default; it has to be NULL because it wasn't supplied before the default will be inserted.
     
    monkey likes this.
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    This behavior is an absolute for all releases prior to 12c. With 12c,t he new ON NULL clause will cause Oracle to populate the column with the default value even when a NULL is explicitly inserted into the column.
     
    monkey likes this.
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Monkey,

    Giving default means, If you didn't insert any value that time it will insert default value.
    If you gave any value to that column then that value will insert in that column not default value.

    SQL> INSERT INTO dog(dogID, dname) VALUES (1, 'Labrador');
    SQL> INSERT INTO dog(dogID, dname,dschool) VALUES (2, 'Labrador2','Dogsch1');
    SQL> INSERT INTO dog VALUES (3, 'Labrador3','');

    3 ROWS created.

    SQL> SELECT * FROM dog;

    DOGID DNAME DSCHOOL
    ---------- ---------- ----------
    1 Labrador ss
    2 Labrator2 Dogsch1
    3 Labrator3
     
    monkey likes this.