5

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?

musefan
  • 47,875
  • 21
  • 135
  • 185
  • @Jon: Could you please explain exactly where the solution is in the question you seem to think is a duplicate? I'm not familiar with ruby on rails – musefan Jul 10 '14 at 14:54
  • TL;DR: It's not you, it's SQLite. *"When adding a table from scratch, you can specify NOT NULL, which is what you're doing with the ":null => false" notation. However, you can't do this when adding a column. SQLite's specification says you have to have a default for this, which is a poor choice. Adding a default value is not an option because it defeats the purpose of having a NOT NULL foreign key - namely, data integrity."* – Jon Jul 10 '14 at 14:55
  • @Jon: and... how does that help me. The solution provided is for ruby on rails.. doesn't help me with my c# application or more specifically my SQL query. I admit it has given me an idea to try something which I hope will work, but I still don't think this is an appropriate duplicate, so please consider removing your close vote – musefan Jul 10 '14 at 14:58
  • 2
    I did, but I 'm not sure what kind of help you expect when SQLite simply does not support what you want. – Jon Jul 10 '14 at 15:10
  • @Jon: It must support it somehow. 1. because the ruby on rails solution claims to work (it must use some SQL behind the scenes). and 2. because the tool I am using (SQLiteStudio) allows me to add NOT NULL columns with no defaults, again it must use some sort of SQL query behind it (unless it is directly modifying the file of course) – musefan Jul 10 '14 at 15:13
  • 3
    ... or unless, behind the scenes, it's taking the often-recommended approach of creating a new table with the column you want, renaming and copying over the data (if any). You can keep insisting that "it must", but the SQLite docs disagree with you, and they'll win the argument. ["If a NOT NULL constraint is specified, then the column must have a default value other than NULL."](http://www.sqlite.org/lang_altertable.html) – Paul Roub Jul 10 '14 at 15:15
  • @PaulRoub: So are you saying that if I create the column with NOT NULL and no default value directly when the table is created it will work? Because that sounds like a solution... and also fits my claim of "it must support it **somehow**" – musefan Jul 10 '14 at 15:17
  • 2
    See, e.g., http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table#805508 - here they're doing it to alter a column, but the same method could be used to add a not-null, no-default column. So, yes, if we *reeeeallly* stretch out the "somehow", you get to still be kind of right. Meanwhile, the `ALTER TABLE` behavior you wanted still isn't there. – Paul Roub Jul 10 '14 at 15:19
  • 1
    When creating a table, no default is strictly necessary, since there's no data yet. When altering, you need to account for the zero-or-more existing rows (the syntax needs to work without checking the row count), whose new columns would need values. – Paul Roub Jul 10 '14 at 15:20
  • @PaulRoub: It's not really stretching anything. I am looking for a solution to my requirements, I am not saying I want a way to make my query magically work. I will investigate your proposed solution and see if it work for my NOT NULL situation. I will post an answer if i works, alternatively you are welcome to answer the question yourself and I will validate if it solves the problem – musefan Jul 10 '14 at 15:22

2 Answers2

9

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

Community
  • 1
  • 1
musefan
  • 47,875
  • 21
  • 135
  • 185
  • 1
    Since you're using SQLiteStudio it might be useful for you to enable debugging of SQL's being executed, so you can do your modifications from SQLiteStudio and use ready SQL queries generated by the application. Here's some details on how to enable debug: http://forum.sqlitestudio.pl/viewtopic.php?f=11&t=349 – Googie Jul 11 '14 at 10:18
  • @Googie: Unfortunately I am not able to get the debug console. I lunch it with `-- -d` ok, but then pressing `F12` has no effect. None of the other functions keys do anything either (except F2 for settings). I am using v2.1.5 – musefan Jul 11 '14 at 11:03
  • 1
    @Googie: It works with `sqlitestudio-2.1.5.exe --debug` ... and im on Windows. Thanks for the info – musefan Jul 11 '14 at 11:07
  • The `F12` trick is for Windows only. If you're using Unix/Linux, it's a bit more complicated and we can take this to chat if you like. – Googie Jul 11 '14 at 11:07
-1

I know its very late to answer to this question and a sort of work around has already been marked as an answer but I faced this problem today and the table had a lot of data to copy it to and fro.

I developed a simple solution to the error. Since the error said that it cannot create a NOT NULL column with the default value NULL, I decided to try and provide a default value. It worked like a charm.

For example, the query from the original question would look like

ALTER TABLE MyTable ADD COLUMN Col1 nvarchar NOT NULL DEFAULT ''

If the column is integer you can provide a default value of something like 0.

I hope this helps someone.

GreatDharmatma
  • 627
  • 5
  • 12