Hi Trejkaz,
What version of Derby did you use to create the original schema? This
behavior (COLUMN_DEF = NULL) goes back at least as far as Derby
10.10.1.1. There is no semantic difference between a column which is
declared without a default and a column which is declared as DEFAULT NULL.
The following script shows the current behavior:
ij> connect 'jdbc:derby:tmpdbs/db0;create=true';
ij> drop table t;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T' because it does not
exist.
ij> drop table s;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S' because it does not
exist.
ij> create table t( a int, b int );
0 rows inserted/updated/deleted
ij> create table s( a int default null, b int );
0 rows inserted/updated/deleted
ij> insert into t(b) values (1);
1 row inserted/updated/deleted
ij> insert into s(b) values (1);
1 row inserted/updated/deleted
ij> select * from t;
A |B
-----------------------
NULL |1
1 row selected
ij> select * from s;
A |B
-----------------------
NULL |1
1 row selected
ij> call syscs_util.syscs_register_tool( 'databaseMetaData', true );
0 rows inserted/updated/deleted
ij> select column_def from table( getColumns( null, 'APP', 'T', 'A' ) ) t;
COLUMN_DEF
--------------------------------------------------------------------------------------------------------------------------------
NULL
1 row selected
ij> select column_def from table( getColumns( null, 'APP', 'S', 'A' ) ) t;
COLUMN_DEF
--------------------------------------------------------------------------------------------------------------------------------
NULL
1 row selected
ij> call syscs_util.syscs_register_tool( 'databaseMetaData', false );
0 rows inserted/updated/deleted
Thanks,
-Rick
On 11/12/15 3:53 PM, Trejkaz wrote:
Hi all.
I'm seeing some weird stuff in our database schema while trying to
improve sanity for migrations.
Basically I'm noticing that tables we migrated from earlier schemas
have "no default" (COLUMN_DEF = null), whereas tables we create today
seem to have COLUMN_DEF = NULL.
I'm wondering whether there is any semantic difference between the
two, because as far as I know, if you don't put a default in for a
column, the default for that column is already null. Is that not the
case? And if it is not the case, why does DEFAULT NULL even result in
the schema being different?
TX
