SQLite Primary Key

SQLite is an open-source database system just like SQL database system. It is a lightweight and serverless architecture which means it does not require any server and administrator to run operations and queries. It is widely used by the developer to store the data within the applications. It is preferable for small datasets without much effort. It is used to develop embedded software and run on various operating systems like Windows, Mac OS, and Linux.

PRIMARY KEY

• PRIMARY KEY is a type of constraint that is used to apply one or more columns of a table to restrict to maintain data integrity. The primary key helps us to ensure that there is no duplicate record with the same token or ID.
• In a table when we define the primary key constraint to a particular column then our table stores all the records in a particular order by which the the data retrieval is very fast.
• PRIMARY KEY of one table can be worked as a FOREIGN KEY for another table which help to join multiple table.
• Since PRIMARY KEY contains UNIQUE entries within it. With the help of PRIMARY KEY, we can easily update or delete the data of any record in the table.
• PRIMARY KEY does not allow a store NULL value and cannot be empty.

CREATE PRIMARY KEY

Let’s understand with the help of an example. Suppose we want to create a table that consists of employee name, employee ID, employee city, and employee department name. If you clearly observe, then you might be thinking in which column you have to apply PRIMARY KEY. So it is very easy, Let me help you in the above table employee’s name can be the same even an employee’s city or department may be the same but every employee has a unique ID. Because it is the only thing that differentiates one employee from another through their IDs.

Syntax:

CREATE TABLE GeekForGeeks 
( 
emp_id varchar(255) PRIMARY KEY,
emp_name varchhar(255),
emp_city varchar(255),
em_dept varchar(255)
)

Explanation:

We have created a GeeksForGeeks table, in which their is a four columns emp_name, emp_city, emp_id and emp_dept where me make emp_id as a PRIMARY KEY because this is the only column that never contains duplicate ID and differentiate each employee based on their employee id.

Since PRIMARY KEY contains UNIQUE entries within it. With the help of PRIMARY KEY, we can easily update or delete the data of any record in the table.

Add Primary Key

SQLite does not allow to define PRIMARY KEY into a existing table. We have to define it at the creation of new table. But we can achieve this by creating new table with defined structures, then copy the data of old table to new table.

Step 1: Create a new table with PRIMARY KEY.

CREATE TABLE new_table
 (
    ID INTEGER PRIMARY KEY,
    name varchar(255),
    age INTEGER
);

Step 2: Now Copy all data from the old table to the new table.

INSERT INTO new_table (ID, name, age) 
SELECT old_id, name, age FROM old_table;

Step 3: Rename tables.

ALTER TABLE old_table RENAME TO old_table_backup; 
ALTER TABLE new_table RENAME TO old_table

Explanation:

In the first step, we created a new table called ‘new_table‘ and made the ‘ID‘ column a new PRIMARY KEY. In the second step, we have performed the INSERT operation by inserting all the data from old_table‘ to ‘new_table‘, in the second step ‘old_id‘ is assumed as a unique identifier column in the original table ‘old_table‘. In the third step, we have to perform a renamed operation and make ‘old table‘ the ‘old_table_backup‘ and ‘new_table‘ as ‘old_table‘. In this way, we can add the PRIMARY KEY to the existing table..

Drop Primary Key

We can remove the PRIMARY KEY constraint after it has been defined and declared with the help of ALTER TABLE Commands.

Syntax:

ALTER TABLE GeeksForGeeks DROP PRIMARY KEY

Explanation: In the above query, the DROP command will remove the PRIMARY KEY Constraint from the column on which it was applied.

Composite Key – Multiple Primary Key

SQLite allows one primary key per table but we can define multiple primary keys in the form of Composite Key. Composite keys combine multiple columns and create unique identifier for each row.

CREATE TABLE GeekForGeeks  ( 
emp_id varchar(255),
emp_name varchhar(255),
emp_city varchar(255), 
em_dept varchar(255) 
PRIMARY KEY (emp_id,emp_name)
)

Explanation: In the above query we combine (emp_id,emp_name) and make a composite key which acts as a PRIMARY KEY and esnure that only a Unique value will be stored.

Conclusion

The PRIMARY KEY constraint ensures data integrity by preventing duplicate entries and expedites data retrieval. It serves as a unique identifier and supports operations like updates and deletions. Adding a PRIMARY KEY to an existing table involves creating a new table and transferring data. Removal of a PRIMARY KEY is possible with the ALTER TABLE command.