SQLite Forum

alter table "table" add column "name" varchar not null throws Cannot add a NOT NULL column with default value NULL
Login

alter table "table" add column "name" varchar not null throws Cannot add a NOT NULL column with default value NULL

(1.1) By juslintek on 2020-07-23 14:55:35 edited from 1.0 [source]

Good day

  • Compile or install latest SQLite.
  • Create a single table with field id int
  • Alter the table and add a column to it which is not null

Here is SQL:

create table permissions
(
    id               bigint unsigned auto_increment
        primary key,
    name             varchar(191)         not null,
    guard_name       varchar(191)         not null,
    for_organization tinyint(1) default 0 not null,
    created_at       timestamp            null,
    updated_at       timestamp            null
)
    collate = utf8mb4_unicode_ci;


alter table "permissions" add column "title" varchar not null;

(2) By Stephan Beal (stephan) on 2020-07-23 14:39:01 in reply to 1.0 [link] [source]

Create a single table with field id int ... Alter the table and add a column to it which is not null

As a rule, you're much more likely to get useful responses if you'll post your SQL instead of expecting all potential responders to come up with their own variation of it. Likewise, a copy/paste of the sqlite3 CLI session is always more useful than a summary of what sqlite3 said.

(3) By juslintek on 2020-07-23 14:51:57 in reply to 2 [link] [source]

Here is the SQL:

create table permissions
(
    id               bigint unsigned auto_increment
        primary key,
    name             varchar(191)         not null,
    guard_name       varchar(191)         not null,
    for_organization tinyint(1) default 0 not null,
    created_at       timestamp            null,
    updated_at       timestamp            null
)
    collate = utf8mb4_unicode_ci;


alter table "permissions" add column "title" varchar not null

(4) By Ryan Smith (cuz) on 2020-07-23 15:10:12 in reply to 1.1 [link] [source]

Please see this discussion on the subject: https://sqlite.org/forum/forumpost/21c7b443aa

It elaborates on why this is a thing.

(6) By juslintek on 2020-07-23 15:38:46 in reply to 4 [link] [source]

Why does it work on MacOS then? As well how can I add required column then? I prefer to have an error that field value is missing, then have an error, that NOT NULL, the default value cannot be NULL lol. That's kind of obvious, why even try to add a default value for the field. Why it doesn't do that when a new table is created, why do it only on ALTER?

(5) By David Raymond (dvdraymond) on 2020-07-23 15:35:29 in reply to 1.1 [link] [source]

Basically ALTER TABLE doesn't distinguish between if it's running on an empty table vs. a table with records.

If there were any records they would all get the default of NULL while you're declaring it to be NOT NULL. And so it says "nope, can't do it" without checking if the table is empty and it would be ok.

(7) By juslintek on 2020-07-23 15:40:12 in reply to 5 [link] [source]

That makes sense. Guess then I will add with a default value and later remove default value. :D

(8) By David Raymond (dvdraymond) on 2020-07-23 17:00:23 in reply to 7 [link] [source]

Can't answer all the questions, but let me see if I can explain some of what's going on.

When you do an ALTER TABLE ADD COLUMN, all that gets updated in the file is the sqlite_master table. No changes are made to any of the actual records on the disk. That makes it super quick even if you have billions of records in the table.

The on disk format for a record has all field values stored in the order they are in the table. When you come along and read a record it says "here are the 5 data types, followed by the 5 values." If SQLite is reading from a table that actually has 6 columns, it gets that data and uses it for fields 1-5. Then it says "well, there's nothing here for column 6, so let's use its DEFAULT value"

So DEFAULT in SQLite is both the normal "use this value on insert if it's not specified for this field" but it's also "use this value when reading from disk if there's nothing there for this field"

(I'm sure I'll be corrected if I explained it wrong)