Re: [sqlite] DEFAULT values replace explicit NULLs?
[EMAIL PROTECTED] wrote: Dennis Cote <[EMAIL PROTECTED]> wrote: 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. Remember that PRIMARY KEY implies NOT NULL. -- D. Richard Hipp <[EMAIL PROTECTED]> Yes it does, so to follow the letter of the law, SQLite should throw an error when the user tries to insert a NULL into an "integer primary key" column. It doesn't do that, and I don't think it should be changed either. I was just pointing out that a NULL doesn't always get inserted just because the user EXPLICITLY entered that value in an insert statement as was suggested by Derrell and others. Dennis Cote
Re: [sqlite] DEFAULT values replace explicit NULLs?
On 3/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > > 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. > > > > Remember that PRIMARY KEY implies NOT NULL. That's not 100% true. In ms sql server this behaviour is a user option. You can set the 'identity' property for a table. This allows you to explicitly specify the key column on insert (unique constraint still applies), or the server inserts the value for you and fails the insert if you specify a key value. It's primarily used in replication so the related keys don't change.
Re: [sqlite] DEFAULT values replace explicit NULLs?
Dennis Cote <[EMAIL PROTECTED]> wrote: > > 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. > Remember that PRIMARY KEY implies NOT NULL. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] DEFAULT values replace explicit NULLs?
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
Re: [sqlite] DEFAULT values replace explicit NULLs?
The current implementation is correct. According to the '92 std (remembering that the default default value of a column is null) the procedure for constructing a new row is (conceptually) 1. Construct a row containing the default value for each column. 2. For each column value specified in the values clause, replace the default value in the corresponding column of the new row with the specified value. There is no exception for a specified value of null. Regards
Re: [sqlite] DEFAULT values replace explicit NULLs?
On 3/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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? IMO the NOTNULL keyword should be a clue for this, if the column has DEFAULT value and NOTNULL flag that should be inserted of DEFAULT value if no value is given, if column has no NOTNULL and has DEFAULT value that should be DEFAULT value is inserted otherwise NULL value is inserted. If no value given into the NOTNULL column that should be raised the syntax error. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] DEFAULT values replace explicit NULLs?
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
Re: [sqlite] DEFAULT values replace explicit NULLs?
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. FWIW, MS SQL Server 2000 does it the same way as SQLite. Specifically inserting a null results in a null in the table, unless there is a 'not null' constraint on the field, of course, in which case inserting a null generates an error.
Re: [sqlite] DEFAULT values replace explicit NULLs?
> 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. That's the behaviour I've gotten from all the databases I've tried.
Re: [sqlite] DEFAULT values replace explicit NULLs?
Default is only supposed to apply on insert, and if no value is specified. If you explicitly insert a null it should be null, not the default. On 3/3/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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?
Re: [sqlite] DEFAULT values replace explicit NULLs?
[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
[sqlite] DEFAULT values replace explicit NULLs?
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? -- D. Richard Hipp <[EMAIL PROTECTED]>
