Thomas Chust wrote:
On Fri, 3 Mar 2006, [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
Is the DEFAULT value for a column suppose to replace
an explicit NULL value? Or does the DEFAULT value only
get used if no values for an insert is specified? What
is the correct SQL behavior?
SQLite does the latter - the DEFAULT value is only used
if no value is given for the column. If you insert an
explicit NULL value then a NULL value is inserted instead
of the DEFAULT value. Ticket #1705 says this is
incorrect.
Which is right? The current SQLite implementation or
ticket #1705?
I don't know which is "right" but I certainly have a strong
preference. If I
explicitly insert a value into a column, it's because I want *that*
value
inserted -- even if the value I insert is NULL. If I don't insert
any value,
then I expect the DEFAULT value, if one is specified for the column
to be
inserted in that column.
Derrell
Hello,
I can only second this statement. I would consider it very
counterintuitive to have another values inserted instead of the
explicitly specified one.
cu,
Thomas
I believe that SQLite's current behavior matches the SQL standard and
should not be changed.
It is worth noting that SQLite's behavior is not completely consistent.
If the column is declared to be an 'integer primary key', then SQLite
will insert a "default" value even when the user explicitly supplies a
NULL value in an insert statement.
sqlite> create table t(a integer primary key, b);
sqlite> insert into t values(NULL, NULL);
sqlite> insert into t values(NULL, 1);
sqlite> select * from t;
1|
2|1
In this case SQLite does not insert the explicitly supplied NULL value.
It substitutes a "default" value that it determines internally.
To be completely consistent SQLite would have to be changed to require
these insert statments to be entered with a column list as below. Now
the user has not supplied an explicit NULL value for column a, and it is
more consistent for SQLite to substitute its rowid value.
sqlite> insert into t(b) values(NULL);
sqlite> insert into t(b) values(1);
I don't think any such change should be made, since it will probably
break many applications, but it worth noting.
Dennis Cote
