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');
But 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.