Creating a 'NOT NULL' SQLite column without specifying a default value

Question

I am trying to create an SQLite database from within a C# WinForms application. I am using the System.Data.SQLite library as found on sqlite.org

I can create the database fine, and I can create tables fine too. The problem comes when I need to add a new column where I don't want to allow nulls, but I don't want to specify a default value either.

For creating a table I would use a query such as this:

CREATE TABLE MyTable (ID INTEGER PRIMARY KEY AUTOINCREMENT)

The problem occurs when I then run this query:

ALTER TABLE MyTable ADD COLUMN Col1 nvarchar NOT NULL

This is producing the following error:

SQL logic error or missing database Cannot add a NOT NULL column with default value NULL

I understand why this might be a problem if the tables contained data, but all the tables are empty (they have only just been created).

I am also able to manually add the NOT NULL columns without defaults to the database using a separate tool (SQLiteStudio).

So why am I not able to do this? Is there something wrong with my query?

Answer 1

Although SQLite does support altering tables, it only supports a small subset of commands. This means that it is not possible to directly alter a table to add a column with any specific constraints.

However, you can specify any column constraints you want if you specify them when initially creating the table. For example:

CREATE TABLE MyTable (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Col1 nvarchar NOT NULL
)

This works perfectly for what I need to do. I am fortunate in that my requirement was to add the columns directly after creating the table, so it didn't take much code rewriting to merge the whole process (create table and columns) into a single query.

---

If the requirement is to alter an existing table and to add new columns then it is much more tricky, but still possible. The solution here would be to rename the current table (as a temp), create a new table (with your original table name) that has the new column, then copy the data from the temp table into the new one, finally delete the temp table.

This process is described here, in the "Making Other Kinds Of Table Schema Changes" section.

Here is a potential solution for doing this