Primary Key in SQL: Syntax, Properties and Benefits

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. By the end of this post, you’ll not only understand the theoretical aspects of primary keys but also acquire practical skills for their application, which will help you optimize your database designs for maximum efficiency and reliability.

Table of Contents

• Understanding the Concept of a Primary Key
• Why Do We Need Primary Key in SQL?
• How to Create Primary Key in SQL
  • Syntax of the Primary Key Constraint
  • Creating a Primary Key in SQL Using SQL Server Management Studio
  • Creating a Primary Key in SQL Using Code
  • Adding a Primary Key to an Existing Table
• Types of Primary Keys
• Understanding the Properties and Rules of an SQL Primary Key
  • Uniqueness and Non-Nullability
  • Limitations and Considerations
• Benefits of Using Primary Key Constraints
• Further Learning

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 explore the concept of primary key in SQL, why it’s so important, and how it works in a database.

What is Primary Key in SQL?

In SQL, a primary key 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 big box of different toys, and each toy has a unique number on it. 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

Table 2: EmployeeTasks

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.  

If you are wondering how to learn SQL for Data Science, here is the SQL Course by Intellipaat.

Why Do We Need Primary Key in SQL?

If you’re just getting started with Primary Keys in SQL and wonder why it’s 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 like the organizers of your data, preventing duplicates and keeping your information reliable, much like unique catalog numbers in a library.

3. Speedy Data Search

They’re like a well-organized contact list in your phone, helping the database quickly find and access the data you need.

4. Connecting the Dots

Primary keys helps different tables in a database communicate, similar to using a friend’s username to connect on social media.

5. Order and Structure

They bring order and structure, essential 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.

How to Create Primary Key in SQL

In this section, we’ll learn about two methods to create a primary key in SQL: using the graphical user interface (GUI) of SQL Server Management Studio (SSMS) or using code. We’ll start with the easier, more visual approach the Management Studio offers. This way, you can choose the method that best suits your comfort level and needs. But before that, let’s discuss the syntax of the Primary Key Constraint.

Syntax of the Primary Key Constraint

Let’s break down how to write the SQL command for creating 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 indicates that adding a constraint name is optional.

This syntax sets up a new table in your database and verifies that each entry in your specified column is unique, which is the primary role of a primary key.

Creating a Primary Key in SQL Using SQL Server Management Studio

To create a primary key column, you can use the graphical user interface (GUI) of SQL Server Management Studio by following these steps.

1. Start by opening your database, then find and right-click on the table where you plan to add the primary key.

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 a fundamental part of making a database, as it helps make sure each piece of 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. Subsequently, we will incorporate a table within it, culminating in the establishment of a primary key for this table through straightforward SQL code. This will help you see how a primary key helps in keeping each record distinct and easy to find.

Step 1: Create a Database

First, we need a database within which our table will reside. The SQL syntax for creating a database is straightforward:

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: Create a Table with a Primary Key

Now, 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’s 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 insert some data into the Users table. Note that each UserID must 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

To see the data in the table along with the primary key:

SELECT * FROM Users;

Step 6: Understanding Primary Key Constraints

The primary key constraint confirms that no two rows can have the same UserID. If you try to insert another record with an existing UserID, the database will reject it. For example:

INSERT INTO Users (UserID, FirstName, LastName, Email) VALUES (1, 'Carol', 'Davis', '[email protected]');

This will result in an error because there’s already a user with UserID = 1

Adding a Primary Key to an Existing Table

To add a primary key to an existing table in SQL, you can use the ALTER TABLE statement along with the ADD CONSTRAINT clause. 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. In the ADD CONSTRAINT clause, you specify the name of the primary key constraint (in this example, PK_ExistingTable) and the column(s) that should make up the primary key (replace ColumnName with the actual column name or a comma-separated list of column names if your primary key consists of multiple columns).

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);

Preparing for SQL Interviews? Check out our list of most asked SQL Interview Questions with Answers.

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 guidelines that make sure your database works smoothly and efficiently.

Uniqueness and Non-Nullability

• Uniqueness: This is like having a unique identifier for each person in a group. In a database, a primary key ensures that each row is distinct from the others. No two rows can share the same primary key value, making each one easily identifiable.
• Non-Nullability: This means that every row must have a primary key value. It cannot be left blank. It’s like saying every player in a team must have a jersey number – no exceptions.

Limitations and Considerations

• Size Constraints: Primary keys have a size limit (typically 900 bytes). This means the data used as a primary key should not be too long or complex.
• Choice of Primary Key: Selecting the right column as a primary key is crucial. It should be a column that will always have unique, non-null values.
• Performance Impact: While primary keys improve data retrieval speed due to indexing, they can also impact performance negatively if not used wisely, especially in large tables or with complex composite keys.
• Alterations and Maintenance: Changing a primary key can be challenging, especially if the table is large or the primary key is linked to other tables as a foreign key. Careful planning and maintenance are required to ensure data integrity.

Benefits of Using Primary Key Constraints

Primary keys are like the secret ingredient that makes your database work better. Here are the two big benefits they offer:

1. Unique Identity for Every Row
   What It Means: Just like every person has a unique ID number, a primary key gives each row in a table its own special identifier.
   Why It’s Great: This means every piece of data can be easily found, and there’s no confusion. You won’t have two pieces of data accidentally mixed up because each one is clearly marked.
   
2. Speeds Up Data Access
   What It Means: Each primary key has a unique index, like a super-efficient filing system.
   Why It’s Great: This makes your database work faster. When seeking specific data, the database can swiftly navigate to the precise location, analogous to directly turning to a page in a book rather than sifting through each page.

Further Learning

Starting your SQL journey with primary keys is just the beginning. To keep improving, you can explore more SQL topics like foreign keys and how to organize your database (that’s what ‘normalization’ is about). Try using what you learn in real-life examples, like making a small database for a project. It’s also really helpful to learn about how to design a database well. Joining online groups/ communities can be super useful for getting tips and keeping up with new ideas. Plus, there are lots of online courses that start easy and gradually get more advanced. Remember, SQL is a big field that’s always changing, so keep learning and practicing to get really good at it.

For any queries that you have on SQL, ask them in Intellipaat’s SQL Community.