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.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Introduction of DBMS (Database Management System) A Database Management System (DBMS) is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small application
8 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read
Window Functions in SQL SQL window functions are essential for advanced data analysis and database management. They enable calculations across a specific set of rows, known as a "window," while retaining the individual rows in the dataset. Unlike traditional aggregate functions that summarize data for the entire group, win
7 min read