How to use PRIMARY KEY in SQLite

SQLite is a database management system, which is used to manage the data of the relational database just like MySQL, it also contains a lot of constraints such as UNIQUE, PRIMARY KEY, and FOREIGN KEY like other databases.

So what are constraints and how do they work in SQLite? This write-up is a comprehensive guide about constraints and on how to use the PRIMARY key constraint in SQLite.

What are the constraints in SQLite

Constraints are the principles, according to which data of the same data type is inserted into the table, it organizes the structure of the column and also ensures the reliability of the data which is stored in the table. There are many constraints, some of them are:

Primary key
Foreign key
Unique constraint
Default constraint
Check constraint

What is the PRIMARY KEY in SQLite

A primary key is the unique column of the table, which ensures the entries inserted in the column should be unique and is also used to refer to the foreign key of other tables. The table can be created without using the primary key, but if the table is using the primary key, then only one primary key can be assigned to the table. Primary keys are very essential when creating a database that has a variety of tables, and to create relationships among the different databases, the primary key is used.

A primary key must follow these rules:

The column of the primary key cannot be defined with the NULL value
The data inserted in the rows of the column of the Primary key should be unique
The primary key should be referred to as the particular foreign key of another table
The table can contain only one primary key

A primary key cannot be a NULL value in the databases, but in the case of SQLite, it can be assigned a NULL value, because of the “long-standing coding oversight”.

A table should have only one primary key, but multiple columns can be defined in a single primary key, when multiple columns are being used as a single primary key, then it is called a composite primary key.

How many ways to add a primary key to any table in SQLite

There are two ways to assign a primary key while creating a table, which are:

To the single column of a table
To the multiple columns of a table

How to create a table assigning a primary key to one column in SQLite

We can create a table by assigning a primary key to a single column, its general syntax will be:

CREATE TABLE TABLE_NAME (column_name1 <datatype> PRIMARY KEY NOT NULL, column_name2 <datatype>);

The explanation of this syntax is as:

The clause of CREATE TABLE is used to create a table
Type the table name instead of table_name
Type the column name instead of column_name1, and also write its data type
Use a clause of PRIMARY KEY, if you are assigning the column as a primary key, and also define it as either NULL or NOT NULL
Type the name second column replacing column_name2

To understand it, consider an example: we create a table of school students, having the unique student ids, named, (std_id), and student names, named, (std_name). In this table, students names can be the same but students ids cannot be the same, so we assign the primary key to std_id as:

CREATE TABLE school_students (std_id INTEGER PRIMARY KEY NOT NULL, std_names);

The table of school_students has been created, having a single column as a primary key.

How to create a table assigning a primary key to multiple columns in SQLite

We can create a table by assigning a primary key to multiple columns, its general syntax will be:

CREATE TABLE TABLE_NAME (column_name1 <datatype> , column_name2 <datatype>, column_name3 <datatype>, PRIMARY KEY(column_name1, column_name2));

In the above syntax, we defined the primary key at the end of the statement, with the names of the column in parentheses that are to be included in the primary key.

Again to understand this, we consider an example of a table employees_data, we will create it with three columns which are emp_id, emp_name, and emp_dep, and after this we assign emp_id and emp_name as a PRIMARY KEY:

CREATE TABLE employees_data (emp_id INTEGER, emp_name TEXT, emp_dep TEXT,emp_name PRIMARY KEY(emp_id, emp_email));

The table has been created with the primary key having two columns in it.

How to add a primary key to the existing table in SQLite

We cannot add a primary key to the existing table in SQLite using ALTER clause, but to assign a primary key to a table in SQLite, we will follow the steps:

The foreign key constraint should be checked off
Rename the table to some other name
Create a new table that has the same structure, which was created before
Copy the data from that table to this table
Delete the renamed table
In the end, turn on the foreign key constraints

We have a table in a database, named, students_data, which has no primary key and its contents can be displayed using:

SELECT * FROM students_data;

To assign the “id” a primary key, we will run the following commands:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE students_data RENAME TO new_students_data;
CREATE TABLE students_data (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, attendance INTEGER NOT NULL);
INSERT INTO students_data SELECT * FROM new_students_data;
DROP TABLE new_students_data;
COMMIT;
PRAGMA foreign_keys=ON;

To check whether the primary key is assigned to the column named, id, run the command:

PRAGMA table_info([students_data]);

The primary key has successfully been assigned to the table students_data.

How to delete the primary key constraint in SQLite

Like other databases, we cannot drop the constraint by using the DROP and ALTER commands, to delete the PRIMARY KEY constraints we should follow the same procedure we opt for adding the constraint to an existing table and redefine the structure of the table without defining a primary key to any column. Let us consider the above example again of adding a primary key, and we delete the primary key as:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE students_data RENAME TO new_students_data;
CREATE TABLE students_data (id INTEGER NOT NULL, name TEXT NOT NULL, attendance INTEGER NOT NULL);
INSERT INTO students_data SELECT * FROM new_students_data;
DROP TABLE new_students_data;
COMMIT;
PRAGMA foreign_keys=ON;

Conclusion

A primary key is very useful especially to establish relationships of a table with others, as a foreign key always refers to the primary key of the table, moreover, a table has only one primary key, but its fields can be one or more than one. In this article, we have discussed how the primary key is used in SQLite and also discussed with examples how a primary key is assigned to one or more columns as well as to the table which is already existing without a primary key.