0

In linux, on the command line (sqlite3), I use the following version of Sqlite:

SQLite version 3.7.6.3

In an sqlite tutorial (http://sqlite.org/autoinc.html) I have read the following statement: 

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly 
given a value, then it will be filled automatically with an unused integer,
usually one more than the largest ROWID currently in use.

I have the following table (.schema addresses):

CREATE TABLE addresses (
id int primary key not null,
ipaddr text not null,
locked int,
date text not null,
quater int,
count not null);
CREATE INDEX addresses_index1 on addresses (ipaddr);

Now, if I am inserting the following row as mentioned above (no values for id), it does not work:

insert into addresses (ipaddr, locked, date, quater, count) values
('0.0.0.0', 0, '2016/12/13', 0, 1);

I get the error message

Error: addresses.id may not be NULL

But the tutorial said that I can do it exactly this way.

What am I doing wrong?

Wolfgang Adamec
  • 8,374
  • 12
  • 50
  • 74

1 Answers1

2

It's integer primary key, not int primary key. The not null is redundant with integer primary key.

laalto
  • 150,114
  • 66
  • 286
  • 303
  • Thanks a lot. But what is the difference between int and integer? – Wolfgang Adamec Dec 13 '16 at 14:21
  • 1
    http://stackoverflow.com/questions/7337882/what-is-the-difference-between-sqlite-integer-data-types-like-int-integer-bigi check that link out that describes the difference between INT and INTEGER. Note the comment on that page with 21 upvotes to the comments. When it comes to autoincrementiing, your column **has to be** of datatype INTEGER even though naming INT gives you the same affinity as INTEGER. – zedfoxus Dec 13 '16 at 14:21
  • 1
    `integer primary key` aliases with ROWID and can be autofilled. `int primary key` is a primary key with integer affinity but it does not alias with ROWID and does not get the auto-filling properties. – laalto Dec 13 '16 at 14:23