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.
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.
Following is the syntax of creating Primary Key Constraint on column.
CREATE TABLE tablename
(column1 INTEGER PRIMARY KEY,
column2 TEXT,
column3 TEXT);
In above syntax, we are creating Primary Key Constraint on “column1”.
Following is the example of adding a primary key constraint on the table using the CREATE TABLE statement.
CREATE TABLE BOOK
(Book_id INTEGER PRIMARY KEY,
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');
sqlite> SELECT * FROM BOOK;
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');
sqlite> SELECT * FROM BOOK;
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.
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.