SQLite | Tables | PRIMARY KEY Constraint

Tags:

A PRIMARY KEY constraint makes a column, or a combination of columns, the table’s primary key.

What is a PRIMARY KEY constraint?

A table has one primary key. Its values must uniquely identify rows.

CREATE TABLE table_name (column_name PRIMARY KEY, ...);

For a composite primary key:

CREATE TABLE table_name (column1, column2, ...,
  PRIMARY KEY (column1, column2, ...));

SQLite permits NULL in some primary-key declarations, but duplicate non-NULL values—or duplicate combinations in a composite key—are not allowed.

create table user (id int primary key, name text);
insert into user values (1, 'devkuma');
insert into user values (2, 'araikuma');
insert into user values (1, 'kimkc');

The last statement fails with UNIQUE constraint failed: user.id because 1 already exists.

PRIMARY KEY on an INTEGER column

When a column is declared exactly as INTEGER PRIMARY KEY, omitting its value during insertion causes SQLite to assign a value automatically.

CREATE TABLE table_name (column_name INTEGER PRIMARY KEY, ...);

The generated value is normally one greater than the largest value currently stored.

Automatic row identifiers require the exact declaration INTEGER PRIMARY KEY. A declaration such as INT PRIMARY KEY does not have the same behavior.

create table user (id integer primary key, name text);
insert into user (name) values ('devkuma');
insert into user (name) values ('araikuma');
insert into user (name) values ('kimkc');

1|devkuma
2|araikuma
3|kimkc

You may also provide an explicit, unique number:

insert into user values (8, 'happykuma');

The next insertion without an ID receives 9 because the current maximum is 8:

insert into user (name) values ('mykuma');

1|devkuma
2|araikuma
3|kimkc
8|happykuma
9|mykuma

Deleted key values can be reused explicitly:

delete from user where id = 9;
insert into user values (9, 'yourkuma');

SQLite normally allows values of a different storage class in a typed column:

create table numtest1 (id integer);
insert into numtest1 values ('hello');

However, an INTEGER PRIMARY KEY accepts only values convertible to integers:

create table numtest2 (id integer primary key);
insert into numtest2 values (10);
insert into numtest2 values ('7');
insert into numtest2 values ('Hello');

'7' is converted to an integer, but 'Hello' produces Error: datatype mismatch.