When you try to add a new column to an empty table using ALTER TABLE, and this column is defined as NOT NULL, SQLite gives an error: Error: Cannot add a NOT NULL column with default value NULL. I think this is not correct because an empty table does not need a default value for the ADD COLUMN to succeed. Creating a table with a NOT NULL column is possible.

Example creating a table with NOT NULL columns, which succeeds:

CREATE TABLE test (Column1 INT NOT NULL, Column2 INT NOT NULL);

Example creating the same table in two steps, which fails:

CREATE TABLE test (Column1 INT NOT NULL);
ALTER TABLE test ADD COLUMN Column2 INT NOT NULL;

Both operations should be equivalent for empty tables. For non-empty tables the error message is as expected.

This matters in practice for Ruby on Rails users, because it causes certain database migrations to fail.

Whether this is a Rails problem or a SQLite problem (or not a problem at all) I guess is a matter of interpretation. I'm posting here to open the discussion, so that hopefully we can figure out the most appropriate place to fix this behavior.

I've opened a discussion on the Rails Github issues again:
Github issues

The real use case would be to allow users of Rails to add reference columns using the same interface as other SQL databases. As is, we run into this error and it feels somewhat mysterious because Rails' ActiveRecord ORM tries to present a consistent interface for SQLite/postgres/MySQL.

I acknowledge that various workarounds are possible, but Rails discourages manually editing database schemas. Forcing users to manually edit the schema means that SQLite is being treated differently, and is more complex to use, than other database back-ends.

If SQLite was some obscure, special-purpose database, I think a workaround would make more sense. But it's being used as the 'development' database in Rails 6 by default, so it's the first one that most users are going to have experience with. It's kind of jarring to create a new Rails project, try adding a reference column, then bam... you're 1 step beyond the tutorial and you're diving through layers to understand what's happening.

It would be interesting if someone here could comment. Do you see this as:

• A Rails problem
  
• A SQlite problem
  
• Not a problem
  
• A problem between interfaces which is minor and shouldn't be fixed

I hope you aren't trying to say that one should try to get the DBMS schema right up front, for all time. That path leads to ossification and denormalization.

My largest SQLite-based app has had dozens of schema changes over its lifetime, and more than once, I've had to add a DEFAULT clause per my other response in order to allow the alteration.

That said, it is exceedingly rare for these modifications to be done to empty — or even empty-able — tables, so adding the special-case check probably couldn't kick in very often in practice. Perhaps this is your actual point?

If the table is empty, you can also just adjust the schema directly in the sqlite_master table (although you must then close and reopen the database before using that table; if you do not, then it can cause problems). (This also works to make other adjustments to the schema, although you should not add or remove tables in this way, nor should you touch the page numbers; if adjusting a table or index which contains data, then you must be more careful; read the file format documentation before attempting to adjust anything in the sqlite_master table at all, or else you will probably break it.)

The error you got is correct for the general case: you can't add a NOT NULL column without giving a DEFAULT clause, because all of the pre-existing rows effectively get set to NULL, and you just told SQLite that column can't be NULL.

There is an edge case where your ALTER TABLE statement could in principle work, that being when the table is empty, which is also why your one-step creation works. SQLite currently doesn't have a special-case check for this condition, so you fall into the general case above.

All of this suggests a possible workaround:

   ALTER TABLE test ADD COLUMN Column2 INT NOT NULL DEFAULT 0;

Whether that particular default is appropriate to your application is another question entirely.