p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle (http://p2p.wrox.com/forumdisplay.php?f=105)
-   -   Default value is not inserting values while creating tables. (http://p2p.wrox.com/showthread.php?t=83039)

arbab March 24th, 2011 06:46 AM

Values are not inserting through 'Default' while creating tables.
 
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

You mean to say that 'Default' will not work to insert values.
 
@ 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.


All times are GMT -4. The time now is 12:56 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.