alter table behavior changed?

[Geoff hendrey <ge...@yahoo.com>]

in an alter table statement, I can do: 

ADD COLUMN column-definitionand column-definition is defined as:

{
    NOT NULL |
    [ [CONSTRAINT constraint-Name]
    {
        CHECK (searchCondition) |
        {
            PRIMARY KEY |
            UNIQUE |
REFERENCES clause
        } 
    }
}
Since there is no way to say "NULL" (NOT NULL may be specified), one would assume that the default behavior when one omits "NOT NULL", is to produce a column that allows NULL. But derby 10.4 is acting as if NOT NULL is the default. Has something changed in 10.4?


 -geoff

Re: alter table behavior changed?

[Bryan Pendleton <bp...@amberpoint.com>]

> Since there is no way to say "NULL" (NOT NULL may be specified), one 
> would assume that the default behavior when one omits "NOT NULL", is to 
> produce a column that allows NULL. But derby 10.4 is acting as if NOT 
> NULL is the default. 

Hi Geoff,

I'm not seeing the behavior you're seeing. Perhaps you are doing something
different than I? Here's a short script:

ij> connect 'jdbc:derby:brydb;create=true';
ij> create table t (c1 int);
0 rows inserted/updated/deleted
ij> describe t;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
C1                  |INTEGER  |0   |10  |10    |NULL      |NULL      |YES

1 row selected
ij> alter table t add column c2 varchar(10);
0 rows inserted/updated/deleted
ij> describe t;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
C1                  |INTEGER  |0   |10  |10    |NULL      |NULL      |YES
C2                  |VARCHAR  |NULL|NULL|10    |NULL      |20        |YES

2 rows selected

ij> insert into t (c1) values (1);
1 row inserted/updated/deleted
ij> select * from t;
C1         |C2
----------------------
1          |NULL

1 row selected

Can you post a more complete script demonstrating the problematic behavior?

thanks,

bryan