db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Difference between having no default and having DEFAULT NULL
Date Tue, 17 Nov 2015 14:40:32 GMT
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
>


Mime
View raw message