Sqlite Primary Keys

SQLite: Primary Keys

This SQLite post explains how to create, add, and drop a primary key in SQLite with syntax and examples.

What is a primary key in SQLite?

In SQLite, a primary key is a single field or combination of fields that uniquely defines a record. A table can have only one primary key.

TIP: While the SQL-89 and SQL-92 standards do not allow a NULL value in a primary key, SQLite does allow a NULL under certain circumstances. We strongly recommend against using a NULL value in a primary key.

Create Primary Key (CREATE TABLE statement)

A primary key can be created when you execute a CREATE TABLE statement in SQLite.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQLite is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);
table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the primary key.

pk_col1, pk_col2, ... pk_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQLite. We will start with a very simple one where our primary key consists of just one column.

For example:

CREATE TABLE employees
( employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

In this example, we've created a primary key on the employees table called employees_pk. It consists of only one column - the employee_id column.

We could have used the alternate syntax and created this same primary key as follows:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

Both of these syntaxes are valid when creating a primary key with only one field.

If you create a primary key that is made up of 2 or more columns, you are limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.

For example:

CREATE TABLE customers
( last_name VARCHAR NOT NULL,
  first_name VARCHAR NOT NULL,
  address VARCHAR,
  CONSTRAINT customers_pk PRIMARY KEY (last_name, first_name)
);

This example creates a primary key on the customers table called customers_pk that is made up of a combination of the last_name and first_name columns. So each combination of last_name and first_name must be unique in the customers table.

Add Primary Key

If your table already exists and you wish to add a primary key later, you can not use the ALTER TABLE statement to create a primary key. Instead, you must create a new table with the primary key and copy the data into this new table.

Syntax

The syntax to add a primary key to a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;
table_name

The name of the table to modify. This is the table that you wish to add a primary key to.

old_table

The name of the original table that will be left behind after you have created the new table with the primary key added.

constraint_name

The name of the primary key.

pk_col1, pk_col2, ... pk_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to add a primary key to an existing table in SQLite. So say, we already have an employees table with the following definition:

CREATE TABLE employees
( employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to add a primary key to the employees table that consists of the employee_id. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO old_employees;

CREATE TABLE employees
(
  employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

INSERT INTO employees SELECT * FROM old_employees;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've created a primary key on the employees table called employees_pk which consists of the employee_id column. The original table will still exist in the database called old_employees. You can drop the old_employees table once you have verified that your employees table and data are as expected.

DROP TABLE old_employees;

Drop Primary Key

In SQLite, you can not use the ALTER TABLE statement to drop a primary key. Instead, you must create a new table with the primary key removed and copy the data into this new table.

Syntax

The syntax to drop a primary key from a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;
table_name

The name of the table to modify. This is the table that you wish to remove the primary key from.

old_table

The name of the original table that will be left behind after you have created the new table with the primary key removed.

Example

Let's look at an example of how to remove a primary key from an existing table in SQLite. So say, we already have a suppliers table with the following definition:

CREATE TABLE suppliers
( supplier_id INTEGER,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR,
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

And we wanted to drop a primary key from the suppliers table. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE suppliers RENAME TO old_suppliers;

CREATE TABLE suppliers
(
  supplier_id INTEGER,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR
);

INSERT INTO suppliers SELECT * FROM old_suppliers;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've dropped the primary key on the existing suppliers table. The original table will still exist in the database called old_suppliers. You can drop the old_suppliers table once you have verified that your suppliers table and data are as expected.

DROP TABLE old_suppliers;

Copyright 2020. All Rights Reserved