Summary: in this tutorial, you will learn how to use SQLite PRIMARY KEY constraint to define the primary key for a table.
Introduction to SQLite primary key
A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.
SQLite allows you to define primary key in two ways:
First, if the primary key consists of one column, you use the PRIMARY KEY column constraint to define the primary key as follows:
1 2 3 4 | CREATE TABLE table_name( column_1 NOT NULL INTEGER PRIMARY KEY, ... ); |
Second, in case primary key consists of more than 2 columns, you use the PRIMARY KEY table constraint to define the primary as the following statement.
1 2 3 4 5 6 | CREATE TABLE table_name( column_1 NOT NULL INTEGER, column_2 NOT NULL INTEGER, ... PRIMARY KEY(column_1,column_2,...) ); |
In SQL-standard, the primary key column must not contain NULL values. It means that the primary key column has an implicit NOT NULL constraint.
However, to make the current version of SQLite compatible with the earlier versions, SQLite allows the primary key column to contain NULL values.
SQLite primary key and rowid table
When you create a table without specifying the WITHOUT ROWID option, SQLite adds an implicit column called rowid that stores 64-bit signed integer. The rowid column is a key that uniquely identifies the row within its table. The table that has rowid column is called rowid table.
If a table has the primary key that consists of one column, and that column defined as INTEGER, exactly INTEGER in any cases, such as,INTEGER,integer, etc., then this primary key column becomes an alias for the rowid column.
Notice that if you assign another integer type such as: BIGINT, UNSIGNED INT, etc., to the primary key column, this column is not the alias for the rowid column.
Because the rowid table stores data as a B-Tree, querying and sorting data using rowid are very fast. It is faster than using a primary key that is not an alias of the rowid.
Another important note is that if you declare a column with the INTEGER type and PRIMARY KEY DESC clause, it does not become an alias for the rowid column.
Creating SQLite primary key examples
The following statement creates a table named countries with the country_id column as the primary key.
1 2 3 4 | CREATE TABLE countries ( country_id INTEGER PRIMARY KEY, name text NOT NULL ); |
Because the primary key of the countries table consists of one column, we define the primary key using PRIMARY KEY column constraint.
You can use the PRIMARY KEY table constraint to define the primary key that consists of one column. It works just fine as the following statement:
1 2 3 4 5 | CREATE TABLE languages ( language_id integer, name text NOT NULL, PRIMARY KEY (language_id) ); |
However, with the table whose primary key consists of more than one column, you must use PRIMARY KEY table constraint to define the primary key.
The following statement creates the country_languages table whose primary key consists of two columns.
1 2 3 4 5 6 7 8 9 | CREATE TABLE country_languages ( country_id integer NOT NULL, language_id integer NOT NULL, PRIMARY KEY (country_id, language_id), FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (language_id) REFERENCES languages (language_id) ON DELETE CASCADE ON UPDATE NO ACTION ); |
Adding SQLite primary key example
Unlike other database systems e.g., MySQL, PostgreSQL, etc., you cannot use the ALTER TABLE statement to add a primary key to an existing table.
To work around this, you need to:
- First, set the foreign key check off.
- Next, rename the table to another table name (old_table)
- Then, create a new table (table) with the exact structure of the table you have been renamed.
- After that, copy data from the old_table to the table.
- Finally, turn on the foreign key check on
See the following statements:
1 2 3 4 5 6 7 8 9 10 11 12 13 | PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table RENAME TO old_table; CREATE TABLE table ( ... ); INSERT INTO table SELECT * FROM old_table; COMMIT; PRAGMA foreign_keys=on; |
The BEGIN TRANSACTION starts a new transaction. It ensures that all the subsequent statements execute successfully or nothing executes at all.
The COMMIT statement commits all the statements.
Let’s create a table name cities without a primary key.
1 2 3 4 5 6 7 8 | CREATE TABLE cities ( id INTEGER NOT NULL, name text NOT NULL ); INSERT INTO cities (id, name) VALUES (1, 'San Jose'); |
Now to add the primary key to the cities table, you need to perform the script above:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE cities RENAME TO old_cities; CREATE TABLE cities ( id INTEGER NOT NULL PRIMARY KEY, name text NOT NULL ); INSERT INTO cities SELECT * FROM old_cities; DROP TABLE old_cities; COMMIT; PRAGMA foreign_keys=on; |
If you use SQLite GUI tool, you can use the following statement to show the table’s information.
1 | PRAGMA table_info([cities]); |
In this tutorial, we have shown you how to use PRIMARY KEY constraint to define the primary key of the table.