Our journey will take us through the practical aspects of primary keys, including how to create them in SQL. You’ll learn about the syntax of the primary key constraint and how to define a primary key when creating a table. You will understand, by the end of this post, not just the theoretical background behind primary keys but also some practical skills as to how to use them, to make your designs optimize your databases to the point of maximum efficiency and reliability.
Table of Contents
Check out this SQL for Data Science tutorial created by Intellipaat for Data Science enthusiasts:
Understanding the Concept of a Primary Key
When you start learning about databases, especially with SQL (Structured Query Language), you’ll often hear about something called a “Primary Key.” Think of it as a super important concept that helps keep the database organized and running smoothly.Let’s discuss what a primary key is in SQL, why it’s so crucial, and how it works within a database.
What is Primary Key in SQL?
A primary key in SQL is a special column or set of columns in a database table that uniquely identifies each row in that table. It verifies that no two rows have the same primary key value and that every row has a primary key value, making each piece of data distinct and easily accessible.
Imagine you have a huge box of various toys, and on each of them, it is written some unique number. In a database, each piece of information (like each toy) is stored in a row in a table, and the primary key is like that unique number. It’s a special column, or sometimes a combination of columns, that makes sure each row is different from all the others.Here’s what makes a primary key special:
It’s Unique (Uniqueness): Just like no two toys have the same number, no two rows in a table can have the same primary key. This uniqueness means you can find and talk about any row in the table without getting confused.
It always has a Value (Non-Nullability): A primary key can never be empty (what we call ‘NULL’ in database language). Every row must have a primary key value, ensuring that every piece of data can be identified.
Here are two tables with an example of a primary key and a foreign key relationship below:
Table 1: Employees
| EmployeeID | FirstName | LastName | Department |
| 1 | Debanjan | Biswas | HR |
| 2 | Akash | Mondal | IT |
| 3 | Mukul | Sharma | Sales |
Table 2: EmployeeTasks
| TaskID | TaskDescription | EmployeeID |
| 101 | Review Reports | 1 |
| 102 | Code New Feature | 2 |
| 103 | Sales Presentation | 3 |
It establishes a relationship between the tasks and the employees, where each task is assigned to a specific employee.
So, in this example:
In the Employees table, EmployeeID is the primary key.
In the EmployeeTasks table, EmployeeID is a foreign key that references the primary key EmployeeID in the Employees table.
Why Do We Need Primary Key in SQL?
If you are just starting with Primary Keys in SQL and wonder why it is so important, let’s break down why we need primary keys in SQL databases in a way that’s easy to understand:
1. Unique Identification
Just like everyone having a unique name, a primary key ensures each row in a SQL table is distinct. It’s key for finding and working with specific data.
2. Keeping Data Tidy and Accurate
Primary keys are sort of like the organizers of your data, eliminating duplicated instances and keeping your information reliable, just like unique catalog numbers in a library.
3. Fast Searching
They’re exactly like an organized contact list within your phone-that allows the database to really and easily locate and retrieve data you need.
4. Connecting the Dots
Primary keys help tables of different databases talk to one another, much like using a friend’s user name to connect on social media.
5. Order and Structure
They bring order and structure, which is necessary for managing a large and complex database, like having a well-organized filing system.
In short, think of primary keys in SQL as the secret to keeping a database unique, fast, and well-connected. They’re like the backbone of a neatly organized system, ensuring everything runs smoothly and efficiently.
Get 100% Hike!
Master Most in Demand Skills Now!
How to Create Primary Key in SQL
We’ll learn in this section how to create a primary key. You can create it either via SQL Server Management Studio’s GUI or via T-SQL script. We are going to begin with the graphical, easier-to-do approach which is offered by Management Studio. You can always select the way which you will find more convenient. But let’s discuss some preliminary information regarding Primary Key Constraint syntax.
Syntax of the Primary Key Constraint
Now let’s break down how to write the SQL command to create a primary key. This command is known as the SQL PRIMARY KEY syntax:
To create a new table with a primary key, you would use the following structure:
CREATE TABLE table_name (
column1 data_type,
...,
[CONSTRAINT constraint_name] PRIMARY KEY (column1)
);
In this structure:
table_name is what you want to name your new table.
column1 is the column in your table where you want to set the primary key.
constraint_name is a name you choose for this particular rule or constraint. This part is optional – you don’t have to name your constraints, but it can be helpful, especially in larger databases.
The […] around CONSTRAINT constraint_name shows that adding a constraint name is optional.
This syntax creates a new table in your database and ensures that every entry in your designated column is unique, which is the primary function of a primary key.
Creating a Primary Key in SQL Using SQL Server Management Studio
You can also create a primary key column using the graphical user interface of SQL Server Management Studio. This is how you can do it.
1. Open your database and locate the table for which you want to add the primary key. Right-click on it.
Creating a Primary Key in SQL Using SQL Server Management Studio
2. Then, right-click on the column name and choose the option to set it as the primary key.
3. After doing this, you’ll notice a key symbol appears next to the column, indicating that it has been successfully set as the primary key column.
Creating a Primary Key in SQL Using Code
Creating a primary key in SQL is an essential aspect of creating a database, which ensures that all information in a table is unique. Let’s walk through an easy example to show how this works. We’ll start by making a basic database, and then adding a table to it. Next, we will add a table to it, which we will end by creating a primary key for this table using simple SQL code. This will help you understand how a primary key is useful in maintaining uniqueness of each record and makes it easy to locate.
Step 1: Create a Database
We need a database to hold our table. The SQL syntax to create a database is as follows:
CREATE DATABASE ExampleDatabase;
Step 2: Use the Database
Before creating a table, we need to define that we’re working in our newly created database:
USE ExampleDatabase;
Step 3: Define a table with a primary key
Let’s create a table. In this example, we’ll make a simple ‘Users’ table. The primary key is typically an ID that uniquely identifies each record. We’ll use ‘UserID’ as our primary key.
CREATE TABLE Users (
UserID int NOT NULL,
FirstName varchar(255),
LastName varchar(255),
Email varchar(255),
PRIMARY KEY (UserID)
);
In this table:
‘UserID’ is an integer and cannot be NULL. It is our primary key
FirstName, LastName, and Email are other fields with the data type varchar (255)
Step 4: Insert Data into the Table
Let’s add some data in the Users table. Note that each UserID should be unique.
INSERT INTO Users (UserID, FirstName, LastName, Email) VALUES (1, 'Intellipaat', 'Software', '[email protected]');
INSERT INTO Users (UserID, FirstName, LastName, Email) VALUES (2, 'spardn', 'writer', '[email protected]');
Step 5: Query the Table
Display data in the table with primary key
SELECT * FROM Users;
Step 6: PRIMARY KEY Constraints
Primary key constraint should ensure that no two rows can have the same value for UserID. If you attempt to insert another record with an existing UserID, the database will not accept it. For instance:
INSERT INTO Users (UserID, FirstName, LastName, Email) VALUES (1, 'Carol', 'Davis', '[email protected]');
This would raise an error because a user with UserID = 1 exists
Adding a Primary Key to an Existing Table
SQL’s ALTER TABLE Statement can be utilized to add the primary key in an existing SQL table with its ADD CONSTRAINT keyword. Here’s how you can do it step by step:
Suppose you have an existing table named ExistingTable and you want to add a primary key to it.
Step 1: Connect to your database and select the appropriate database
USE YourDatabaseName;
Step 2: Add a primary key to an existing table
ALTER TABLE ExistingTable
ADD CONSTRAINT PK_ExistingTable PRIMARY KEY (ColumnName);
Use the ALTER TABLE statement to modify the structure of the existing table.ADD CONSTRAINT clause, you would specify the name of the primary key constraint, say PK_ExistingTable, and the column(s) that should form the primary key. You replace ColumnName with the actual column name or a comma-separated list of column names if your primary key consists of more than one column.
After executing these SQL statements, your existing table will have a primary key constraint.
Here’s an example that adds a primary key to an existing table named Employees with an EmployeeID column as the primary key:
USE YourDatabaseName;
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);
Types of Primary Keys
In SQL, primary keys come in two main types, each serving the purpose of uniquely identifying records in a table:
Simple Primary Key:
- This involves just one column.
- It’s like giving each person a unique ID number.
Example: A “UserID” column in a user table.
Composite Primary Key:
- This uses two or more columns combined.
- It’s like using both a person’s birth date and name to identify them.
Example: In an “OrderDetails” table, combine “OrderID” and “ProductID” to uniquely identify each row.
Both types verify that each record is unique, but the choice depends on the data structure and requirements of your database.
Understanding the Properties and Rules of an SQL Primary Key
When learning about primary keys in SQL, it’s important to grasp their basic properties and rules. These are like the rules that ensure your database works effectively and efficiently.
Uniqueness and Non-Nullability
- Uniqueness: This is a unique identifier for any individual in a group. In a database, the existence of primary keys ensures that each row is unique in comparison to all others.Two rows cannot have the same value for their primary key; hence, every one can be easily identified.
- Non-Nullability: This means that every row in a table must contain a value for the primary key. It should not be empty. It’s like saying every player in the team must have a jersey number-no exception.
Limitations and Considerations
- Size Limits: The data to be selected as the primary key must meet a size constraint. Typically this size limit of 900 bytes applies. End.
- Choice of Primary Key: The appropriate column to be chosen as the primary key is very important. It should be a column that will always have unique, non-null values.
- Performance Impact: Primary keys are indexed, so they improve the speed of data retrieval. However, poor usage can, under certain specific conditions, quite significantly hamper performance, especially when dealing with large tables or complex composite keys.
It is very difficult to change a primary key if the table is large or if the primary key is referenced by other tables as a foreign key. This requires careful planning so that the data remains in its integrity.
Benefits of Using Primary Key Constraints
Primary keys are that secret ingredient that makes your database work better. And here are the two major benefits they offer:
- A unique identity for each row
What It Means:Since each person has been assigned a specific ID number, the primary key assigns a unique identifier to each row in the table.
Why It’s Great: This results in easy searchability of all data and removes ambiguity. Two pieces of data won’t end up being mixed up for each other; instead, they will each be clearly tagged.
- Accelerates Data Access
What It Means: Each primary key has a unique index, which is like a super-efficient filing system.
Why It’s Great: It causes your database to run much quicker, mainly by speeding up specific searches through direct location as would be seen turning directly to the page one requires instead of searching the entire book for a desired item.
Further Learning
That is just the start of your SQL journey with primary keys. To continue to move ahead, you can start exploring other SQL topics like foreign keys and database organization – this is what ‘normalization’ is all about.Try applying whatever you have learned on some real-life examples – create a small database for a project, for example. It is also very useful to learn about how to design a database well. Joining online groups/community can be very useful in getting tips and keeping up with ideas. Plus, there are lots of online courses that start easy and gradually get more advanced. Remember, the field of SQL is very massive and is undergoing constant changes so keep learning, practicing, to really get good with it.
Our SQL Courses Duration and Fees
Cohort starts on 11th Jan 2025
₹15,048
Cohort starts on 18th Jan 2025
₹15,048