SQLite PRIMARY KEY

SQLite PRIMARY KEY constraint represents a column or a combination of several columns that through their values uniquely identify each row in the table.

An SQLite table can contain only one PRIMARY KEY constraint. As in SQL, a primary key can be defined in SQLite in two ways:

Syntax

Here is the syntax of SQLite PRIMARY KEY constraint:

The first way to define a SQLite primary key and the most often used, is when a single column is used to create the primary key.

CREATE TABLE table_name(
	column_name1 datatype PRIMARY KEY AUTOINCREMENT,
	column_name2 datatype NOT NULL,
	column_name3 datatype,
	...
);

The second way to create a primary key is when using multiple columns to form a SQLite primary key.

CREATE TABLE table_name(
	column_name1 INTEGER NOT NULL,
	column_name2 INTEGER NOT NULL,
	column_name3 VARCHAR,
	...
	PRIMARY KEY(column_name1,column_name2)
);

Example

CREATE TABLE customer_type(
type_id INTEGER PRIMARY KEY AUTOINCREMENT,
type_name VARCHAR(100) NOT NULL,
comments VARCHAR(1000)
);

INSERT INTO customer_type (type_name, comments)
VALUES ('Individual', 'test 1');
INSERT INTO customer_type (type_name, comments)
VALUES ('Company', 'test 2');

SELECT * FROM customer_type;

Table of customer_type

type_id	type_name	comments
1	Individual	test 1
2	Company	test 2

Primary key with multiple columns

CREATE TABLE customer_contracts(
customer_id INTEGER NOT NULL,
contract_id INTEGER NOT NULL,
contract_date DATE,
amount INT,
PRIMARY KEY(customer_id,contract_id)
);

INSERT INTO customer_contracts (customer_id, contract_id, contract_date, amount)
VALUES (1, 1, date(), 100);
INSERT INTO customer_contracts (customer_id, contract_id, contract_date, amount)
VALUES (1, 2, date(), 150);
INSERT INTO customer_contracts (customer_id, contract_id, contract_date, amount)
VALUES (2, 1, date(), 120);
INSERT INTO customer_contracts (customer_id, contract_id, contract_date, amount)
VALUES (3, 1, date(), 180);
INSERT INTO customer_contracts (customer_id, contract_id, contract_date, amount)
VALUES (3, 2, date(), 140);

SELECT * FROM customer_contracts;

Table of customer_contracts

customer_id	contract_id	contract_date	amount
1	1	2022-12-27	100
1	2	2022-12-27	150
2	1	2022-12-27	120
3	1	2022-12-27	180
3	2	2022-12-27	140

↑