inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

Thomas Kurz
I just stumbled upon the following issue (tested with 3.27.1):

I can do this:
CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE);

But this fails:
ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;

--> Error: Cannot add a NOT NULL column with default value NULL

I think both variants should behave consistently. But I don't know which behavior is the correct one according to the SQL standard.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

J. King-3
On February 13, 2019 5:32:09 PM EST, Thomas Kurz <[hidden email]> wrote:

>I just stumbled upon the following issue (tested with 3.27.1):
>
>I can do this:
>CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id) ON
>UPDATE CASCADE ON DELETE CASCADE);
>
>But this fails:
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES
>mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;
>
>--> Error: Cannot add a NOT NULL column with default value NULL
>
>I think both variants should behave consistently. But I don't know
>which behavior is the correct one according to the SQL standard.
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

This is a limitation of SQLite's current ALTER TABLE implementation. Columns can only be added with a default value of NULL, therefore NOT NULL columns are forbidden.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

Thomas Kurz
> This is a limitation of SQLite's current ALTER TABLE implementation. Columns can only be added with a default value of NULL, therefore NOT NULL columns are forbidden.

I don't think so because this works (shortened here; it also works with REFERENCES...):

ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior of ALTER should be correct whilst CREATE seems to forget to reject the statement.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

Thomas Kurz
> I guess a missing DEFAULT automatically implies DEFAULT NULL, so the behavior of ALTER should be correct whilst CREATE seems to forget to reject the statement.

Sorry, I was wrong about this. The CREATE shows the correct behavior whereas ALTER incorrecty rejects the statement. According to https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html default values for columns without explicit DEFAULT are chosen by type (section "Handling of Implicit Defaults").

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

Graham Holden
On Wednesday, February 13, 2019, 11:06:07 PM, Thomas Kurz
<[hidden email]> wrote:

>> I guess a missing DEFAULT automatically implies DEFAULT NULL, so
>> the behavior of ALTER should be correct whilst CREATE seems to
>> forget to reject the statement.

I suspect the difference is you can CREATE a NOT NULL column with an
(implied) DEFAULT NULL because there are no rows yet, and so long as
you always supply a NOT NULL value when INSERTing, nothing is
violated. However, when trying to add a NOT NULL (implied) DEFAULT
NULL column, any _existing_ rows would violate the condition (since
their values for the new column would be NULL) so it cannot be
allowed. In theory, I think it _could_ be allowed if the table is
empty (but I don't know whether SQLite checks this).



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

Keith Medcalf
In reply to this post by Thomas Kurz

You should probably read the documentation.

https://sqlite.org/lang_altertable.html


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thomas Kurz
>Sent: Wednesday, 13 February, 2019 15:32
>To: SQLite mailing list
>Subject: [sqlite] inconsistent behavior when creating INTEGER NOT
>NULL column without DEFAULT?
>
>I just stumbled upon the following issue (tested with 3.27.1):
>
>I can do this:
>CREATE TABLE test (groupid INTEGER NOT NULL REFERENCES mygroup (id)
>ON UPDATE CASCADE ON DELETE CASCADE);
>
>But this fails:
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL REFERENCES
>mygroup (id) ON UPDATE CASCADE ON DELETE CASCADE;
>
>--> Error: Cannot add a NOT NULL column with default value NULL
>
>I think both variants should behave consistently. But I don't know
>which behavior is the correct one according to the SQL standard.
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: inconsistent behavior when creating INTEGER NOT NULL column without DEFAULT?

J. King-3
In reply to this post by Thomas Kurz
On February 13, 2019 5:47:09 PM EST, Thomas Kurz <[hidden email]> wrote:

>I don't think so because this works (shortened here; it also works with
>REFERENCES...):
>
>ALTER TABLE test ADD COLUMN groupid2 INTEGER NOT NULL DEFAULT 1;

You're correct; I must have been confusing the prohibition on CURRENT_TIMESTAMP with a general one.

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users