Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 24th, 2011, 06:46 AM
Authorized User
Points: 350, Level: 6
Points: 350, Level: 6 Points: 350, Level: 6 Points: 350, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , Pakistan.
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default 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.
__________________
How to do programming?

Last edited by arbab; March 24th, 2011 at 06:50 AM..
  #2 (permalink)  
Old March 24th, 2011, 08:01 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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.
  #3 (permalink)  
Old March 28th, 2011, 03:01 AM
Authorized User
Points: 350, Level: 6
Points: 350, Level: 6 Points: 350, Level: 6 Points: 350, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2008
Location: , , Pakistan.
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default 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
__________________
How to do programming?
  #4 (permalink)  
Old March 28th, 2011, 03:11 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Who said "default does not work"? Please show the query you are using to insert data where the default values are not inserted.
__________________
Joe
http://joe.fawcett.name/
  #5 (permalink)  
Old March 28th, 2011, 06:07 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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...
The Following User Says Thank You to disel2010 For This Useful Post:
arbab (March 29th, 2011)
  #6 (permalink)  
Old April 8th, 2011, 06:39 AM
Authorized User
 
Join Date: Nov 2006
Location: Bangalore, Karnataka, India.
Posts: 31
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via ICQ to debasisdas Send a message via AIM to debasisdas
Default

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.
__________________
Regards
Debasis
The Following User Says Thank You to debasisdas For This Useful Post:
arbab (April 11th, 2011)
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Default value for field when inserting areed24 ASP.NET 3.5 Basics 2 March 25th, 2009 12:51 PM
Inserting values from other tables TheAndruu SQL Language 7 May 27th, 2007 10:47 AM
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
Inserting data into 2 tables chrscote Access ASP 1 August 1st, 2005 09:01 AM
inserting into multiple tables cridley SQL Server 2000 1 January 25th, 2005 12:05 PM



All times are GMT -4. The time now is 11:54 AM.


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