Re: column with default char not defaulting when inserting a record

On Aug 4, 3:02 am, DA Morgan <damor..._at_psoug.org> wrote:
> Baldwin wrote:
> > Hi,
>
> > I got this very interesting issue. I configured a column of a table
> > defaulted to 'A'. Whenever I insert a record into the table without
> > specifying a value in that column, the column is not defaulted to 'A'.
> > I am able to default other char column without any problem. But for
> > this one column, for some reason, it is not defaulting to 'A'. My
> > table got 34 columns. There are foreign keys but not set to the column
> > with the issue. I got a lot of triggers for this table, but i tried
> > disabling them and inserted a record and still didn't work.
>
> > Here is the example. the table and column names are not the actual
> > names.
> > -----------
> > Here are my sql statements.
>
> > insert into mytable(id, name)
> > values(33, 'testme')
>
> > select id, name, status
> > from mytable
> > where name = 'testme'
> > -----------
> > Here is the table definition.
>
> > create table mytable
> > (
> > id NUMBER(10) not null,
> > name VARCHAR2(60) not null,
> > status CHAR(1) default 'A'
> > )
> > -----------
>
> > Any help is greatly appreciated. thanks
> > Baldwin
>
> No issue in any version of 10g or 11g on Windows or Linux (Oracle or
> RedHat).
>
> SQL> create table mytable (
> 2 id NUMBER(10) not null,
> 3 name VARCHAR2(60) not null,
> 4 status CHAR(1) default 'A');
>
> Table created.
>
> SQL>
> SQL> insert into mytable
> 2 (id, name)
> 3 values
> 4 (33, 'testme');
>
> 1 row created.
>
> SQL> SELECT * FROM mytable;
>
> ID NAME S
> --------- --------- -
> 33 testme A
>
> SQL>
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

We are using 10g. We dont have any problem in other columns with default values except for this one column. Actually this is our issue in our two tables.

We also tried to insert and explicitly define the value like :

insert into mytable(id, name, status)
values(33, 'testme', 'A')

And it doesnt work.

But if you update the column like:

update mytable
set status = 'A'
where id = 33

And it completely worked. Received on Mon Aug 06 2007 - 12:48:02 CDT