Primary Key In SQL

Primary Key In SQL

10 mins read2.2K Views Comment
clickHere
Updated on Sep 15, 2023 18:45 IST

Humans identify everything using names. For example, you, I, and everyone have unique names as our identity. There are several situations where two persons have similar first names. In those cases, we have to use their full name ( first and last names together) to identify them as separate persons. If one or more persons have similar full names, we use some other property, such as phone number, to acknowledge the persons individually. So, we always try to devise a way to identify every individual separately.

2022_08_Primary-Key-in-SQL.jpg

In the world of SQL databases, the primary key is like a digital fingerprint for each row in a table. It ensures that every piece of data is unique and easily identifiable. In this article, we’ll explore what a primary key is, why it’s crucial, and how it makes database management more organized and reliable. Whether you’re new to SQL or a seasoned pro, understanding the primary key is key to efficient data management. Let’s get started on this journey into the heart of SQL databases.

Table of Content  

What is Primary Key? 

A primary key is a column (or group of columns) in a relational database table that uniquely identifies each row in the table. It is a special kind of key that has the following properties:

  • Unique: No two rows in the table should have the same primary key value.
  • Non-null: The primary key value should not be null.
  • Stable: The primary key value should not change over time.
  • Easy to use: The primary key value should be easy to remember and use in queries.

A simple example of a primary key in SQL is the id column in a table of customer records. Each customer record has a unique id value, and no two customer records can have the same id value.

Why use Primary Key in SQL?

Benefit Why it is important Example
Uniquely identifies each row of a table Allows you to easily identify and access any row in the table. Customer ID, Product ID, Employee ID, etc. If you want to find the record for a specific customer, you can simply look up the customer’s ID. This is especially important for large tables with many rows.
Prevents duplication of records in a table Ensures that the data in your table is accurate and consistent. If you allow duplicate records, it can be difficult to know which record is the correct one. This can lead to errors and inconsistencies in your data. No two customers can have the same Customer ID, no two products can have the same Product ID, and no two employees can have the same Employee ID
Helps with updating or deleting only specific records Primary Key in SQL helps you to make changes to the data in your table without affecting other records. If you want to delete the record for a specific customer, you can simply specify the Customer ID in your query. This will prevent you from accidentally deleting the records for other customers. The same is true for updating records.
Helps ensure that fields aren’t null Ensures that the data in your table is complete. If a field is null, it means that the value for that field is unknown. This can make it difficult to use the data in your table. A primary key column cannot have null values. This helps to ensure that the data in your table is accurate and consistent.
Helps set up relationships between tables Creates a relationship between the two tables, which allows you to query data from both tables at the same time. For example, you could query a table of customers and a table of orders to find all the orders for a specific customer. A primary key in one table can be used as a foreign key in another table.

How to choose a strong Primary Key in SQL?

  • Unique: The primary key value must be unique for each row in the table. This means that no two rows can have the same primary key value. For example, a column for the customer’s email address is not unique, as two customers could have the same email address.
  • Not null: The primary key value cannot be null. This means that every row in the table must have a value for the primary key.
  • Unlikely to change: The primary key value should be unlikely to change over time. This is because changing the primary key value can be difficult and error-prone. For example, a person’s name or address is likely to change over time, so these columns should not be used as primary keys.
  • Small and used in indexes: The primary key should be a small, fixed-size data type. This will improve the performance of queries that use the primary key. The primary key should also be used in indexes. Indexes can improve the performance of queries that search for specific records in the table.
  • Not changed once assigned: The primary key value should not change once it is assigned. This is because changing the primary key value can break relationships between tables.

Examples of Strong Primary Key in SQL

  • Customer ID: It can be treated as a primary key as it is unique for each customer. It is unlikely to change over time, as it is assigned by the company when the customer opens an account.
  • Employee number: This is another primary key that is unique for each employee. It is unlikely to change over time, as it is assigned by the company when the employee is hired.
  • Product ID: The data in this column is a natural key since it is unique for each product. It is unlikely to change over time, as it is assigned by the company when the product is created.
  • Order number: This is a surrogate key that is unique for each order. It is likely to change over time, as new orders are created. However, it is still a strong primary key, as it is unlikely that two orders will have the same order number.
  • Invoice number: This is another surrogate key that is unique for each invoice. It is likely to change over time, as new invoices are created. However, it is still a strong primary key, as it is unlikely that two invoices will have the same invoice number.
  • Universally unique identifier (UUID): A UUID is a 128-bit number that is guaranteed to be unique. It is a good choice for a primary key when there is no natural key that is unique.

Difference between Primary Key, Composite Key, and Foreign Key

Aspect Primary Key Composite Key Foreign Key
Purpose Uniquely identify each row in a table Uniquely identify each row in a table when a single column is insufficient Establish relationships between tables
Example Student ID in a school (Social Security Number, Account Number) Book ID in checkout records referencing Book ID in books table
Explanation Each student has a unique ID used for identification Requires a combination of two or more columns to guarantee uniqueness Links records in one table to records in another table
Uniqueness Each value in the column must be unique The combination of values in the columns must be unique together Values must exist in the referenced table’s column
Constraint Ensures that each record has a unique identifier in the table Ensures that combinations of columns have unique values Ensures that values in the column exist in another table
Example in Use Ensures that no two students have the same ID Used when both your social security number and account number are needed for verification Allows tracking which student checked out which book
In Real Life Social Security Number, Passport Number Combination lock with multiple numbers Barcode on a product linking to its manufacturer
Analogy Each student’s ID card A combination lock Barcode scanner linking to a product database

Read More: Difference between Primary Key and Foreign Key

Primary Key Operations 

The primary Key is always associated with the column or columns in a table. We can create, alter or even drop the primary key from the columns in a table. Let’s go through each of these using an example. 

Single Column Primary Key 

Suppose you have a table named students having columns of name, roll, age, and marks. These are the data for the students of a particular class. Now every student from a similar class will have a unique roll number thus, we can make the roll number column our primary key. 

Let’s create the table of students with roll number as the primary key – 

 
CREATE TABLE Students (
Name VARCHAR(100),
Roll INT,
Age INT,
Marks FLOAT,
PRIMARY KEY (Roll)
);
Copy code

Thus, we place the column name inside the PRIMARY KEY() brackets. This indicates that the column Roll is our primary key, and it should satisfy all the conditions to be a primary key to identify the records uniquely. 

Now suppose you have created a table like this – 

 
CREATE TABLE Students (
Name VARCHAR(100),
Roll INT,
Age INT,
Marks FLOAT
);
Copy code

That is, without defining any column as a primary column. Now you may need to define a column as a primary column. In that case, we have to alter the table and add the primary key constraint with the required column as such – 

ALTER TABLE Students ADD PRIMARY KEY (Roll); 

This will make the Roll column a Primary field. 

Now you may want to remove the primary constraint from any column. In that case, what you have to do is – 

ALTER TABLE Students DROP PRIMARY KEY 

This will remove the single Primary Key you are having in your Students table for the Roll column.  

Multiple Column Primary Key 

Suppose you have a table of students with columns of name, class, age, marks, and roll number. Now all the students from a specific class will have a unique roll number associated with them, that is there will be a single person from class 6 with roll 10, and a single person from class 7 with roll 10, but no two students from these classes will have the same roll number. Thus, we have to take two columns named class and roll them together as a primary key. We cannot take class and roll numbers individually, as the class number and roll number will repeat in the whole dataset. 

Let’s create the table of students with class and roll number as primary keys – 

 
CREATE TABLE Students (
Name VARCHAR(100),
Class INT,
Roll INT,
Age INT,
Marks FLOAT,
CONSTRAINT Class_Roll PRIMARY KEY (Class, Roll)
);
Copy code

This Class and Roll column together will make up the primary key, and Class_Roll is the name given to the new primary key created out of two columns. In short, Class_Roll is the name given to the primary key constraint, which is basically the two columns together. This naming is necessary for identifying the primary key in the table, which is then used for identifying unique records. So, in this case, when we talk about Class_Roll, we are talking about the primary fields made from the columns of Class and Roll together. 

Now, if we want to delete the primary key constraint in the case of multiple column primary key, we have to do this – 

ALTER TABLE Students DROP CONSTRAINT Class_Roll 

Thus, we can remove the primary key constraint from more than one column at once by dropping it using the name we have given to it while creating. 

Now, if we require to create the constraints once again on both the columns, then we have to alter the table in this way – 

ALTER TABLE Students ADD CONSTRAINT Class_Roll PRIMARY KEY (Class, Roll); 

While using alter table statement to add the primary key constraints to the columns in a table, those columns must follow the primary key conditions. The specific columns must not have any duplicate value or null value in them. 

Summary 

Primary Key is an important concept and is helpful in fetching unique records from the table. The primary key helps in preventing redundancy or duplicated values in the dataset. The primary field plays a crucial role in joining two or more tables. We use the primary field while joining two or more tables because this field has unique values to identify each record separately. 

If you do not have any column suitable as a primary field, you should create another column having unique values and mark it as a primary field. If you have a single or multiple column(s) suitable to be the primary key, that field(s) should be defined as the primary key. The primary field can be of integer or character data type; it just needs to have all unique and non-null values. 

More Articles in SQL

Basic

Introduction to SQL
Introduction to SQL
This article will teach you basic and advanced concepts of SQL. It concepts, such as what is a Database Management System, what is SQL, SQL Data types, SQL Statements, and...read more
SELECT Statement in SQL
SELECT Statement in SQL
This article will help you understand some of the most common SQL SELECT use cases with examples. Large amounts of data in diverse types are stored in databases. Have you...read more
How to Use LIMIT Clause in SQL
How to Use LIMIT Clause in SQL
As a database administrator or developer, you know how crucial it is to manage data loads and optimize query performance. One of the essential tools in your toolkit is the...read more
How to Use WHERE Clause in SQL
How to Use WHERE Clause in SQL
The WHERE clause in SQL is a powerful tool that filters data in database queries. It specifies the conditions for rows to be included in the result set. The syntax...read more
SQL Operator: Comparison and Arithmetic
SQL Operator: Comparison and Arithmetic
While working with a large dataset you have to perform a different kind of operation to extract or manipulate the the data from the dataset. To perform these operation we...read more
Logical Operators in SQL
Logical Operators in SQL
While working with a large dataset you have to perform a different kind of operation to extract or manipulate the the data from the dataset. To perform these operation we...read more
LIKE Operator in SQL
LIKE Operator in SQL
Logical Operators in SQL are used to connect two or more expressions. In this article, we will discuss one of the logical operators i.e. LIKE operator in detail about how...read more
How to use SQL IN Operator
How to use SQL IN Operator
In the previous article, we have discussed LIKE operator. In this article, we will discuss IN operator in SQL that is also a logical operator.
BETWEEN Operator in SQL
BETWEEN Operator in SQL
In the previous articles, we have discussed LIKE and IN operator. In this article we will discuss BETWEEN operator in SQL that is also a logical operator.
SQL Functions: Aggregate and Scalar
SQL Functions: Aggregate and Scalar
In this article, we will discuss about different SQL Functions: Aggregate and Scalar. Aggregate contains function like min, max, sum count while scalar contains function like now, round, mid etc.
How to use COUNT in SQL
How to use COUNT in SQL
Aggregate functions in SQL are used over a set of values, that return a single value. In this article, we will discuss one of the aggregate function COUNT function. Along...read more
What are Constraints in SQL?
What are Constraints in SQL?
Constraints in SQL are not a mandatory to use while creating the table but they are very helpful to maintain the accuracy, reliability, and integrity of the data during the...read more

Intermediate

How to use ORDER BY Clause in SQL
How to use ORDER BY Clause in SQL
Sorting the result after extracting the data from the dataset is one of the most basic thing we do to get the pattern or highest/lowest values in a particular field...read more
How to Use HAVING Clause in SQL
How to Use HAVING Clause in SQL
When a query is applied to a large dataset, then we get number of records so we need to filter out the specific records. WHERE clause in SQL is used...read more
How to use GROUP BY in SQL?
How to use GROUP BY in SQL?
The GROUP BY clause in sql is used to group the rows that have identical values by one or more columns. In this article, we will briefly discuss how GROUP...read more
How to CREATE TABLE in SQL
How to CREATE TABLE in SQL
When you are working with the large dataset sometime you want to segregate some data and inspect them separately or sometimes you don’t have any table or dataset in that...read more
How to use INSERT in SQL
How to use INSERT in SQL
Storing data is of utmost importance for reproducing the same whenever the need arises and for several other business purposes. These data are stored inside tables, and related tables are...read more
SQL UPDATE: How to Update Data in a Table
SQL UPDATE: How to Update Data in a Table
In this article, we will explore using the SQL UPDATE statement, a powerful tool for modifying existing data within your database tables. First, we will cover the basics of the...read more
How to Use TRUNCATE Command in SQL?
How to Use TRUNCATE Command in SQL?
SQL TRUNCATE TABLE command is used to remove the record from the table. Confused, the SQL DELETE statement also removes the record from the table. So, why use another command...read more
How to Delete a Column in SQL?
How to Delete a Column in SQL?
Sometimes in the dataset, you have some columns that are not of your use but when you run a query it will also get executed that leads to you increase...read more
How to use DROP command in SQL?
How to use DROP command in SQL?
In SQL, the DROP command is used to delete a database, table, index, or view. To use the DROP command, you must be very careful because it permanently deletes the...read more

Advanced

SQL Joins: Inner, Left, Right and Full
SQL Joins: Inner, Left, Right and Full
Understanding SQL Joins is fundamental in database management and data analysis. This article explores the four primary types of SQL Joins: Left, Right, Inner, and Full. Each type uniquely combines...read more
INNER JOIN in SQL
INNER JOIN in SQL
SQL JOINS are used to combine more than two or more tables together to extract the useful data from all the tables. In this article, we will discuss INNER JOIN...read more
SQL LEFT JOIN – Examples and Syntax
SQL LEFT JOIN – Examples and Syntax
SQL JOINS are used to extract the record from two or more interrelated tables. In the previous article, we briefly discussed SQL INNER JOIN. In this article, we will discuss...read more
SQL RIGHT JOIN – Example and Syntax
SQL RIGHT JOIN – Example and Syntax
Right Join in the sql returns all the rows from the right table and the matching entries from both tables. In this article, we will discuss how to use right...read more
How to use UNION in SQL
How to use UNION in SQL
UNION in SQL is similar to the union of two sets in mathematics that is equal to the set of element that are present in both the set. Similarly UNION...read more
Understanding Subqueries in SQL
Understanding Subqueries in SQL
This blog explains subqueries in SQL. We will explore what are nested queries and sub-queries and how they help retrieve data from complex databases.
Understanding SQL Server Data Types
Understanding SQL Server Data Types
An SQL data type is like a tag or a label that defines what kind of value a column in a database table can contain. Data types describe how the...read more
Introduction to Normalization – SQL Tutorial
Introduction to Normalization – SQL Tutorial
This article will cover what Normalization in SQL is, its forms, such as 1NF, 2NF, etc., and examples.
Introduction to Window Functions in SQL
Introduction to Window Functions in SQL
As a beginner, the majority of queries we write in SQL are focused on returning a single piece of data, or a single piece of data per group.
Cursors in SQL: Definition, Types and Lifecycle
Cursors in SQL: Definition, Types and Lifecycle
In this article, we will talk about the definition, types and lifecycle of cursors in SQL. Do take a look to learn about SQL cursors in detail.
Using SQL Pivot – SQL Tutorial
Using SQL Pivot – SQL Tutorial
This article will cover what SQL PIVOT is, how to use SQL PIVOT, and what SQL UNPIVOT is, along with examples.
ACID Properties in SQL
ACID Properties in SQL
ACID (Atomicity, Consistency, Isolation, and Durability) are being used in SQL transaction. In this article, we will briefly discuss about these ACID properties with examples.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments