Primary key and autoincrement in SQLite

How create primary key with autoincrement column in SQLite ? I describe it step by step in this topic.

Launch SQLite3.exe application. Open or create new database. My is test.db.

sqlite> .open test.db

Create table of example t name with column integer. It is may be primary key so add this clause either. Primary key denote UNIQUE NOT NULL value in this column.

sqlite> create table t(id integer primary key, name text);

In SQLite You may either declare affinity of integer for example: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT UNSIGNED BIG INT, INT2, INT8. Affinity datatypes of integer will be assign as INTEGER, but better is use original datatype of SQLite.
You declare id column as PRIMARY KEY. How add autoincrement ? In SQLite You must not to add autoincrement clause as in other database servers. It is sufficient set NULL in id place or omit this column. You may either in any moment set any value in id.

sqlite> insert into t values(null,'note');
sqlite> insert into t values(7,'note');
sqlite> insert into t values(null,'ballpoint');

For show result use select query:

sqlite> select * from t;
1|pencil
2|pen
3|note
7|note
8|ballpoint


If You insert in id value which exists in column the error appear:

sqlite> insert into t values(7,'rubber');

xx2But if You as PRIMARY KEY declare affinity of integer, example INT the autonicrement don’t be work. Belove is my test1.db database:

So if You create objects in SQLite the best is use main datatype of SQLite.