Description:
I've been thinking on a interesting feature. It's a clause on column definition called ON NULL. The problem actually is that a column that has a DEFAULT value AND a clause NULL cannot be inserted/updated with NULL, even with playing with SQL_Modes. This could be a bug of my version, if so report me it please, cause I have no way to pass NULLs from my high level code to de database without parsing it to DEFAULT.
So I think we could implement ON NULL X clause, which will be an alias for:
NOT NULL IF NULL DEFAULT_VALUE=X
Example:
create table mytable(
mynumber INT ON NULL -1 DEFAULT 0
)
insert into table values(); -> Value 0
insert into table values(NULL); -> Value -1
insert into table values(DEFAULT_VALUE); -> Value -1
It will be more flexible if you could specify as a ON NULL value the DEFAULT_VALUE itself
create table mytable(
mynumber INT ON NULL DEFAULT_VALUE DEFAULT 0
)
insert into table values(); -> Value 0
insert into table values(NULL); -> Value 0
insert into table values(DEFAULT_VALUE); -> Value 0
In that context, all would be a lot of flexible, and interesting for ODBC,JDBC programmers, and it will overwrite SQL_MODE politics with specific directives in scripts.
the clause could be a third excludent alternative to NULL/NOT NULL definition. A column could be NULL,NOT NULL or ON NULL X
Thanks for your support and regards,
Marc
How to repeat:
create table mytable(
mynumber INT ON NULL DEFAULT_VALUE DEFAULT 0
)
insert into table values(); -> Value 0
insert into table values(NULL); -> Value 0
insert into table values(DEFAULT_VALUE); -> Value 0
