SQL Primary Key

A Primary Key is an important concept in SQL and is used to uniquely identify one record within a table in the database. This means that no duplicate values or null values can exist in the column that comprises a primary key. This concept is also important in terms of data retrieval and the overall integrity of data within your SQL database. Relational databases use primary keys to establish relationships between tables. In this blog, let’s explore what a primary key is, its types, and how to create it in detail with examples for each.

Table of Contents:

• What are Primary Keys in SQL?
• Properties of Primary Keys in SQL
• Types of Primary Keys in SQL
  1. Simple Primary Key
  2. Composite Primary Key
• How do You Create a Primary Key in SQL?
  • Using CREATE TABLE in SQL
  • Using ALTER TABLE in SQL
  • Creating a Primary Key in SQL using SQL Server Management Studio
• Rules for Primary Keys in SQL
• Benefits of Using the Primary Key in SQL
• Real-world Example
• Common Mistakes to Avoid
• Best Practices
• Conclusion

What are Primary Keys in SQL?

A Primary Key in SQL is a specific column or group of columns that uniquely identifies every record row in a table. The value in the primary key column must be unique so that no two rows can have the same value in the primary key column and cannot contain a NULL value. Each table can have at most one primary key (a primary key can only be one column or a combination of columns). 

Each primary key is automatically indexed for performance reasons. Primary keys are very important for maintaining data quality, they also allow relationships to be built between tables with the use of Foreign Keys. Data in a table can be duplicate and ambiguous if there is no defined primary key.  An effective primary key should be stable, simple, and never change.

Before getting started with primary keys in SQL, let’s create a table named EmployeeDirectory without a Primary Key that can be used as an example for the following concepts.

CREATE TABLE Emp_Dir (
    Emp_ID INT,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Dep VARCHAR(50)
);

INSERT INTO Emp_Dir (Emp_ID, Full_Name, Email, Dep) VALUES
(1001, ‘Aarav’, ‘[email protected]’, ‘Software Development’),
(1002, ‘Isha’, ‘[email protected]’, ‘Human Resources’),
(1003, ‘Rohan’, ‘[email protected]’, ‘Data Analytics’),
(1004, ‘Sneha’, ‘[email protected]’, ‘Marketing’),
(1005, ‘Devansh’, ‘[email protected]’, ‘IT Support’);

Select * from Emp_Dir;

This is how the Employee Directory table looks.

Syntax to create a single-column Primary Key

CREATE TABLE tab_name (
    col_name dtype PRIMARY KEY,
    ...
);

Syntax to create a Composite Primary Key

CREATE TABLE tab_name (
    col1 dtype,
    col2 dtype,
    ...
    PRIMARY KEY (col1, col2)
);

Master Primary Key in SQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Properties of Primary Keys in SQL

A Primary Key in SQL has very specific rules and behaviours that are necessary to ensure the integrity and uniqueness of data in a table. 

• Uniqueness: Every value in the primary key column must be unique. No two rows can have the same primary key value. Every record must be identifiable.
• Cannot be NULL: Primary key columns cannot have NULL values. All rows must have some value for the primary key.
• Only One Primary Key per Table: A table can only have one primary key, but that key can be one or more columns (in the case of a composite key).
• Automatically Indexed: When you define a primary key on a column, almost all SQL databases will automatically create a unique index on that column to make queries execute faster.
• Immutable: A primary key should generally never change during the lifecycle of a record. If you change a primary key, then you will break relationships with the other tables that reference this primary key.
• May be Used in Relationships: Primary keys are normally referenced by foreign keys in other tables to create one-to-many or one-to-one relationships.

Types of Primary Keys in SQL

Though a primary key serves a similar purpose of uniquely identifying each record, it can take several different forms depending on how it is defined in and used by a table:

1. Simple Primary Key

A primary key that consists of a single column. It uniquely identifies each row in a table and ensures that no two rows have the same value in that column.

Example:

CREATE TABLE Emp_Dir (
    Emp_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Dep VARCHAR(50)
);

-- To describe the table 
desc emp_dir;

Output:

Explanation: Here, Emp_ID by itself identifies each employee uniquely.

2. Composite Primary Key

A composite primary key consists of two or more columns combined to uniquely identify a record.

Example:

CREATE TABLE Pro_As (
    Emp_ID INT,
    Pro_ID INT,
    Ass_Date DATE,
    PRIMARY KEY (Emp_ID, Pro_ID)
);

-- To describe the table
desc Pro_As

Output:

Explanation: Here, the combination of Emp_ID and Pro_ID ensures each assignment is unique.

How do You Create a Primary Key in SQL?

In SQL, a Primary Key is significant because it must be unique for each row in a table. You can do this when you create the table, or later by using the ALTER TABLE statement.

Using CREATE TABLE in SQL

This can be used to create a primary key while creating a table, using the CREATE TABLE Statement.

For a Single Column:

During the table creation, only one column is defined as the unique identifier.

Example:

CREATE TABLE Dir (
    Emp_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Dep VARCHAR(50)
);

-- To describe the table 
Desc Dir;

Output:

Explanation: Here, the Emp_ID is defined as the primary key, and using the DESCRIBE Statement, we can verify the table.

For Multiple Columns

To uniquely identify each record, it combines two or more columns.

Example:

CREATE TABLE Att (
    Emp_ID INT,
    Att_Dt DATE,
    Status VARCHAR(20),
    PRIMARY KEY (Emp_ID, Att_Dt)
);

-- To describe the table 
Desc Att;

Output:

Explanation: Here, to ensure row uniqueness, the composite primary key is created using two columns (Emp_ID, Att_Dt).

Using ALTER TABLE in SQL

ALTER TABLE in SQL  is used to add a primary key to an already existing table. 

Example:

-- Consider the same Emp_dir table that we have created without a primary key. Let’s create a primary key using the ALTER TABLE Statement.
ALTER TABLE Emp_Dir
ADD PRIMARY KEY (Emp_ID);

-- To describe the table 
desc Emp_Dir;

Output:

Explanation: Here, it will make the EMP_ID column the primary key in the existing Emp_Dir table.

Creating a Primary Key in SQL using SQL Server Management Studio

We can create a Primary Key in SQL Server Management Studio by following these steps

Step 1: Open SQL Server Management Studio, select the database, and right-click on the table in which you want to add a primary key

Step 2: Right-click on the column name that you want to keep as the primary key and set the primary key option.

Step 3: The changes will be done, and the key will appear next to the column, which confirms that the column is a primary key.

Rules for Primary Keys in SQL

Primary Keys in SQL have a specific set of rules to allow the identification of each row within a table uniquely and reliably. Once a primary key constraint is created upon a table, the database will enforce these rules for you automatically.

Uniqueness

The Uniqueness property ensures that no two rows of the table can share the same value in the primary key column. It ensures that every record is distinct and separately identifiable in the table. The database engine enforces this rule automatically with a unique index. Inserting duplicate values that cause a violation of this rule will raise an error. 

Example:

Consider the Emp_Dir table, where we have set a primary key constraint for Emp_ID. Let us try inserting a non-unique value into it

-- Inserting an Emp_ID = 1003, which already exists in the Emp_Dir table

INSERT INTO Emp_Dir (Emp_ID, Full_Name, Email, Dep)
VALUES (1003, 'Tanvi', '[email protected]', 'Finance');

Output:

Explanation: Since we are trying to insert an already existing Emp_ID, which is set as a primary key, the above error will occur.

Non-Nullability

A primary key column in SQL cannot have NULL values. This non-nullability ensures that each row can be identified uniquely because NULL means “unknown” or “missing.” If the primary key column has even one NULL, it will break this rule. This rule helps apply completeness and integrity to the data in the table. This rule applies to both single-column primary keys and composite primary keys.

Example:

-- Let’s try to insert a NULL value into a primary key column

INSERT INTO Emp_Dir (Emp_ID, Full_Name, Email, Dep)
VALUES (NULL, 'Tanvi', '[email protected]', 'Finance');

Output:

Explanation: Here, this INSERT will cause the error since Emp_ID is a primary key and NULL is not allowed in primary key columns.

Benefits of Using the Primary Key in SQL

• Non-nullable: Prevents null values as primary key columns cannot be null, meaning that every row will have some unique and accessible identifier.
• Data Modelling: Establishes a clearer structure to the database model, utilising a consistent way of referencing records.
• Auto index creation: Primary keys are automatically indexed, meaning no manual index will need to be created and/or maintained, which may increase data performance.
• Stability for orphan records: Ensuring that there are no orphan records when used appropriately with foreign keys, meaning that you cannot have records without their corresponding foreign key records.
• Data validation: Ensures no invalid or incomplete data can enter the database. Primary keys establish uniqueness and prevent null values, making sure you cannot enter all non-unique or incomplete data.

Real-world Example

School Management System

A school management wants to list all the students in the school by ensuring that no two students have the same Student_id.

Example:

-- Here we have created a Student table with Stu_id as a primary key
CREATE TABLE Stud (
    stu_id INT PRIMARY KEY,
    stu_name VARCHAR(50),
    cls_name VARCHAR(50),
    age INT
);

INSERT INTO Stud VALUES 
(1, 'Anjal', 'Grade 1 - A', 6),
(2, 'Rahul', 'Grade 2 - B', 7),
(3, 'Pooja', 'Grade 3 - A', 8);

-- Let’s try inserting a duplicate entry
INSERT INTO Stud VALUES (1, 'Duplicate Entry', 'Grade 4 - B', 9);

Output:

Explanation: Here, stu_id is a primary key so that no two students can have the same student ID, which ensures uniqueness and non-nullability.

Common Mistakes to Avoid 

• Using meaningful columns as primary keys: Using email, phone_number, or student_name as a natural key. This might change with time and may not be truly unique. 
• Allowing the NULL value in the primary key column: Primary keys must be NOT NULL, but in some cases, forgetting to explicitly set NOT NULL (especially with composite keys) can lead to problems. 
• Not defining a Primary Key at all:  Without a primary key defined, it’s much easier to have duplicate records and ambiguous records, so try to assign a primary key. 
• Reusing primary keys: Referring to the same column; For example, SSN, Email, as the Primary Key in multiple tables instead of using Foreign keys correctly. 
• Assuming Application logic will check for uniqueness: Some developers may think that the uniqueness check is done in the app; It should be enforced in the Database. 
• Using Long or Complex Data Types: You should not use VARCHAR, GUID, or other large data types as a primary key unless it is unavoidable – all these things slow down indexing and joins.

Best Practices

• Consider using Surrogate Keys: Use auto-incrementing INT or BIGINT columns (i.e., student_id, emp_id) as the primary keys.
• Always define a Primary Key: Make sure that your table has a uniquely defined primary key to ensure uniqueness and to avoid duplicate records.
• Choose Stable Keys that do not change: Use values that will never change – changing a key is risky and a potential opportunity for errors.
• Keep simple and small primary keys: Smaller keys provide better performance in indexing and in joins (i.e., INT is a better choice than VARCHAR).
• Keep Constraints at the Database level: Let the DBMS manage the uniqueness constraints and integrity by not solely relying on application logic.
• Avoid duplicate key names: For better clarity, name the keys that best align with the scenario, like student_id, course_id, order_id, etc.

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career—without spending a dime!

Explore Program

Conclusion

Primary keys form the foundation of reliable and efficient database design for several reasons. They ensure that each record in a table is unique and help maintain data accuracy and integrity. In any project, using primary keys correctly prevents duplication of data, improves query performance, and makes it easier to manage and group data over time. Avoid mistakes, follow best practices, and always create a table with a clear primary key. In this blog, you have gained knowledge on primary keys in detail.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

SQL Primary Key – FAQs