prevent autoincrementing integer primary key?

Question

I have a sqlite table (sqlite version 3.7.3) where nulls inserted into the primary key column are being undesirably auto-incremented:

sqlite> CREATE TABLE foo(bar INTEGER NOT NULL PRIMARY KEY);
sqlite> INSERT INTO foo(bar) VALUES(NULL);
sqlite> SELECT * FROM foo;
1

In the sqlite docs, it shows that adding the AUTOINCREMENT keyword to the column should create this behavior, but there doesn't appear to be a keyword to prevent the auto incrementing...

I also found that I can build sqlite with the SQLITE_OMIT_AUTOINCREMENT compile option, but I don't want to disable the behavior globally, just for this particular column.

Interestingly, if I don't include the PRIMARY KEY constraint, I get the desired behavior:

sqlite> CREATE TABLE FOO(bar integer NOT NULL);
sqlite> INSERT INTO FOO(bar) VALUES(NULL);
SQL error: foo.bar may not be NULL

How can I define the table so that NULL values are rejected and keep the primary key constraint?

Answer 1

One way to disable autoincrement (outside of recreating the table) when you need to insert data is to use the import tool in sqlite3:

If you have a table like this:

CREATE TABLE [table1] ( [ID] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [col1] TEXT);

If you run the import command on it with your data file:

ID col1
10 abc
20 def

import myfile.txt table1

It will import the rows, and it will disregard the autoincrement feature.

Answer 2

Autoincrement behavior applies only to columns declared as INTEGER PRIMARY KEY. So the easiest ways to disable it are:

• Declare the column as UNIQUE instead of PRIMARY KEY.
• Declare the column type as INT instead of INTEGER.

Note that either one will give you a column with integer affinity instead of being constrained to contain only integers.