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..
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.
Contact Us