Skip to Main Content
  • Questions

  • inserting into a table with defaults

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vinayak .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: June 23, 2005 - 6:53 pm UTC

Version:

Viewed 1000+ times

You Asked

hi,
I have got a table testtab with two columns numcol1 and numcol2
numcol1 has a default value 3

when i insert data into this i need to specify the column name also

like
insert into testtab(numcol2) values(12);
This works

but if i give
insert into testtab values(12);
this fails

i can understand that ORACLE first matches the number of columns
and the number of values in the values clause.

Is there a way i can insert data into the table without
specifying the columnnames so that the default_value is entered
into the table for the column numcol1 and the value that i
specify into numcol2

This may appear silly but please answer



and Tom said...



Lets say you had a table:

create table T ( numcol1 number default 3, numcol2 number default 3 );

what should "insert into t values ( 1 );" do? Why?

What if the table was:

create table t ( numcol1 number default 3, numcol2 number, numcol3 number );

insert into t values ( 1, 2 );

should 1 go into numcol2 or numcol3 -- why (or why not)?

I believe the reason is one of consistency and clarity. You either must supply ALL values for a row -- or you must explicity name the columns you are inserting. Another solution would be to:

create view t_view as select numcol2 from t;

Now you can "insert into t_view values ( 12 )" without having to name the column and without having any of the ambiguity the other syntax would leave us with.

remember in a relational world -- the order of columns is not relevant (there is no such as "order of columns").



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

insert a row with all values default

prashant, May 08, 2002 - 1:59 am UTC

Hi Tom,
This question has been bothering me since long. How one could put a row in a table with all values default, had the table been defined like that. The same problem is with a procedure where if I provide default values to all the parameters and if i want to call it with those values. You have mentioned the possibility in answer to this query but have not in clear terms rejected the possibility. So please answer.

Tom Kyte
May 08, 2002 - 7:28 am UTC

In 9i, you could:


insert into t (c1) values ( DEFAULT );


In 8i and before, you cannot (to what end would a row with all default values be useful anyway??)


I do not understand the second part:

The same
problem is with a procedure where if I provide default values to all the
parameters and if i want to call it with those values.


Are you asking "how do i pass the defaults?" If so, the answer is "just don't pass ANYTHING and the defaults are passed"




Removing defaults

Michael, June 23, 2005 - 11:36 am UTC

I have a table in which 47 columns have a specified default value. The table owner now wants the default removed from 20 of these columns. How do I do this? I tried:
alter table x
modify( column_1 default null);

but this sets the default now to null.
thanks

Tom Kyte
June 23, 2005 - 6:53 pm UTC

that is the only way to do it, once it is defaulted, it will always have a default forever, even if the default is the "default" default of null.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.