SQLite Primary Key Constraint

  Total Views : 15
  Comments : 0
 
  Keywords : SQLite add primary key constraint to column with example, Use sqlite primary key constraint to allow only unique values in column with example, SQLite create primary key constraint on column

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 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 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 NULL value to Primary Key column then it will consider as a 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

(column1 INTEGER PRIMARY KEY,

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 primary key constraint on table using 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 Book_id column must be unique.

 

In SQLite by using PRAGMA command we can see the structure of 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 above output, pk with 1 value implies primary key. Now we will insert data in table using 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 NULL value in Book_id column using 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 above result when we try to insert NULL value in Book_id column then automatically it consider as AUTOINCREMENT and inserted Book_id as 2

 

As we discussed, in SQLite if we try to insert NULL value in Primary Key Column with INTEGER data type then it will consider as a 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 above example, we created table with primary key on single column. Now, we will create a table with multiple columns as primary key.

 

Following is the SQLite statement to create multiple columns as 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 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User