SQLite Primary Key Constraint

Here we will learn SQLite Primary key constraint with example and how to use SQLite Primary Key Constraint to make sure rows identified uniquely with examples.

SQLite Primary Key Constraint

In SQLite, Primary Key Constraint is used to identify the records or rows in a table uniquely. If we set Primary Key Constraint on column then the values whatever we are going to insert in that column must be unique.


The Primary Key Constraint will not allow any Null values and we can able to create only one Primary Key Constraint on table column to identify rows uniquely. We can say that Primary Key Constraint is a combination of Not Null and Unique Constraint.


Generally, in SQLite we can use Primary Key Constraint on a single column in case if we want to create Primary Key Constraint on more than one column then we will call it as Composite Key.


In SQLite, if we pass a NULL value to the Primary Key column then it will consider as an AUTOINCREMENT for that column otherwise it will give unique key violation error.

Syntax of SQLite Primary Key Constraint

Following is the syntax of creating Primary Key Constraint on column.


CREATE TABLE tablename


column2 TEXT,

column3 TEXT);

In above syntax, we are creating Primary Key Constraint on “column1”.

Example of SQLite Primary Key Constraint

Following is the example of adding a primary key constraint on the table using the CREATE TABLE statement.




Book_name TEXT,

Publisher_name TEXT);

In above example we added Primary Key constraint on Book_id column. So the values whatever we are going to insert in the Book_id column must be unique.


In SQLite, by using the PRAGMA command we can see the structure of the table.


sqlite> PRAGMA table_info(BOOK);


cid         name        type        notnull     dflt_value  pk

----------  ----------  ----------  ----------  ----------  ----

0           Book_id     INTEGER     0                       1

1           Book_name   TEXT        0                       0

2           Publisher_  TEXT        0                       0

Here in the above output, pk with 1 value implies the primary key. Now we will insert data in the table using the following statements.


sqlite> INSERT INTO BOOK Values(1,'SQLite-Guide','Shweta');




Book_id     Book_name     Publisher_name

----------  ------------  --------------

1           SQLite-Guide  Shweta

Now we will insert a NULL value in the Book_id column using the following SQLite statement.


sqlite> INSERT INTO BOOK Values(NULL,'SQL-Guide','Shweta');




Book_id     Book_name     Publisher_name

----------  ------------  --------------

1           SQLite-Guide  Shweta

2           SQL-Guide     Shweta

If you observe the above result when we try to insert a NULL value in the Book_id column then automatically it considers as AUTOINCREMENT and inserted Book_id as 2


As we discussed, in SQLite if we try to insert a NULL value in Primary Key Column with INTEGER data type then it will consider as an AUTOINCREMENT for that column.


Now we will try to insert 1 in Book_id column using following SQLite statement.


sqlite> INSERT INTO BOOK Values(1,'SQLite-Guide','Shweta');


Error: UNIQUE constraint failed: BOOK.Book_id

The above statement will violates PRIMARY KEY constraint because Primary Key Constraint will accept only unique values.

SQLite Primary Key on Multiple Columns

In the above example, we created table with a primary key on a single column. Now, we will create a table with multiple columns as primary key.


Following is the SQLite statement to create multiple columns as the primary key.


CREATE TABLE Employee_Master

(first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

CONSTRAINT con_primary_name PRIMARY KEY(first_name,last_name));

When we run above query it will create a composite key on columns first_name and last_name of Employee_Master table because if we create primary key on multiple columns then it will become composite key.


Once we create a primary key on multiple columns those columns won't allow any NULL and duplicates values because all those columns will work as a Primary key.


This is how we can SQLite Primary Key Constraint on table columns to allow only unique records based on our requirements.

Topics Covered