Default value is not inserting values while creating tables.

arbab · March 24th, 2011, 06:46 AM

The table has been created through the query below

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Karachi',
Country char(25),
Birth_Date date)

but the default value is not inserting in their respective column, what's wrong with the query?

Thanks in advance.

disel2010 · March 24th, 2011, 08:01 AM

Hi,

how does your insert sql look like?
Just a guess, but if your insert looks like this:

Code:

insert into customer
values ('SCOTT', 'TIGER4', null, null, 'US', to_date('19-10-1961', 'dd-mm-yyyy'));

You are deliberately overwriting the value for Address and City...

The default value only works if you do something like this:

Code:

 
insert into customer(First_Name ,Last_Name )
values ('SCOTT', 'TIGER');

Hope this helps.

arbab · March 28th, 2011, 03:01 AM

@ disel2010: You mean to say that âDefaultâ does not work, we need to have insert query to fill the columns, I am learning Oracle/SQL commands from a website where I found this:
Sometimes, we want to provide a default value for each column. A default value is used when you do not specify a column's value when inserting data into the table. To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column "Address" to "Unknown" and City to "Mumbai", we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)

Referece:
http://www.1keydata.com/sql/sqlcreate.html

Thanks in advance

joefawcett · March 28th, 2011, 03:11 AM

Who said "default does not work"? Please show the query you are using to insert data where the default values are not inserted.

disel2010 · March 28th, 2011, 06:07 AM

Hi Arbab,

sorry to hear my post wasn't clear.
I was trying to show by example when (and when not) the default value comes into play.
To clarify, please note:
- that the default value only comes into play when a column isn't specified in an INSERT statement...
- that as soon as you do specify a column with a default value, you are overwriting the default value

Also, it's only when you are actually inserting records that the default value can come into play.. The moment you created your table with the DDL statement you only have the metadata describing your database object, not the data itself yet...

debasisdas · April 8th, 2011, 06:39 AM

The keyword default means values (default values ) will be inserted into the specified column of the table if no data is passed in the insert statement (column is ignored). But if data is passed the default value will be over written by the passed value.