What is a primary key in DBMS?

A primary key is like a unique identifier for every row in a database table. It ensures that each record is distinct and helps maintain data integrity. Think of it as the ID card of your data.

What is a primary key?

A primary key is a column (or combination of columns) that uniquely identifies each row in a database table. Every table should have a primary key to keep the data organized.

Let’s look at an example of a simple Students table:

Students

StudentID (Primary Key)

Name

Age

1

Alice

22

2

Smith

20

3

Charlie

23

In this table:

  • The StudentID column is the primary key.

  • Each student has a unique StudentID, ensuring there are no duplicates.

Understanding the purpose of a primary key, but how do we implement one in a database? Let’s look at the syntax and examples of creating a primary key.

Syntax for creating a primary key

We can define a primary key when creating a table or add it to an existing table.

Example 1: Creating a table with a primary key

CREATE TABLE Students (
    StudentID INT PRIMARY KEY, -- Defines StudentID as the primary key
    Name VARCHAR(100),         -- Column for storing student names
    Age INT                    -- Column for storing student ages
);

  • Here, the StudentID is declared as the primary key.

  • This ensures no two students can have the same StudentID.

If we insert data into the table:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);
INSERT INTO Students (StudentID, Name, Age)  
VALUES (1, 'Alice', 22),  
       (2, 'Bob ', 20);  
       
select * from Students; # Printing the student record

The table will look like this:

Students

StudentID (Primary Key)

Name

Age

1

Alice

22

2

Bob

20

Try inserting two rows with the same StudentID to see what happens.

When you execute this query, the database will throw an error similar to:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

This happens because the primary key enforces the rule that each value in the StudentID column must be unique. By trying to insert a duplicate StudentID, the database prevents the operation, ensuring data integrity.

Example 2: Adding a primary key to an existing table

If the table is already created without a primary key, we can add it later:

ALTER TABLE Students 
ADD PRIMARY KEY (StudentID); -- Adds a primary key constraint to the StudentID column

While primary keys are used to maintain data integrity, there may be situations where you need to remove them. Let’s see how to do this.

Syntax for deleting a primary key

We can remove the primary key if we don’t need it anymore.

Example:

ALTER TABLE Students 
DROP PRIMARY KEY; -- Removes the primary key constraint from the table

  • This removes the primary key constraint from the StudentID column.

  • Be careful when doing this, as it can lead to duplicate or empty values in the column.

Now that we know how to create and remove a primary key, let’s explore its key characteristics.

Properties of a primary key

  1. Unique values: No two rows can have the same value in the primary key column.

Students

StudentID

Name

1

Alice

2

Bob

Duplicate StudentID values are not allowed.


  1. Not Null: A primary key column cannot have empty (null) values. For example, If StudentID is left blank, the database will throw an error.

  2. Unchanging: The primary key values should not change frequently to maintain consistency.

  3. Single or Composite: A primary key can be a single column or a combination of columns (composite keyA composite key is a primary key made up of two or more columns. For example, in a table of course enrollments, the combination of StudentID and CourseID can uniquely identify each record.).

Now that we’ve covered the properties of primary keys, let’s evaluate their advantages and limitations to understand their role in database management.

Pros and Cons of a Primary Key

Pros

Cons

This ensures data integrity and Prevents duplicate or null values.

Once set, primary keys are hard to modify.

When you set a primary key, the database automatically creates an index on that column to speed up data retrieval.

Managing primary keys in complex systems can be challenging.

This help in Links tables efficiently.

Indexes created for primary keys can increase storage requirements, especially for large datasets.

To summarize the importance and functionality of primary keys, here are the key points to remember.

Key takeaways of a primary key

  • A primary key ensures data integrity by uniquely identifying each row in a table.

  • We can define a primary key during table creation or add it later.

  • Primary keys automatically create an index, improving search performance.

  • A primary key must always have unique and non-null values.

  • We can use a single column or a combination of columns (composite key) as a primary key.

Ready to learn SQL and become a database expert? Join our Learn SQL course and follow the path to becoming a Database Professional with SQL! Unlock your potential and gain the skills employers seek. Start your journey today!

Frequently asked questions

Haven’t found what you were looking for? Contact Us

What is a primary key and foreign key in DBMS?

  • Primary key: A column (or set of columns) that uniquely identifies each row in a table. It ensures data integrity by preventing duplicate and null values.
  • Foreign key: A column (or set of columns) in one table that refers to the primary key in another table. It establishes a relationship between two tables.

What is a primary key and candidate key in DBMS?

  • Primary Key: A selected candidate key that uniquely identifies rows in a table and ensures no null or duplicate values.
  • Candidate Key: Any column (or set of columns) that can uniquely identify rows in a table. A table can have multiple candidate keys, but only one is chosen as the primary key.

What is the difference between primary key and super key?

  • Primary key: A minimal set of columns that uniquely identify a row, with no extra attributes. It must be unique and not null.
  • Super key: A set of one or more columns that can uniquely identify rows in a table. A super key may contain additional attributes that are not necessary for uniqueness.

Can a key be both primary and foreign?

Yes, a key can be both a primary key in its own table and a foreign key in another table. For example, an EmployeeID column in an Employees table can act as a primary key and also serve as a foreign key in a WorkAssignments table.

Can a primary key be NULL?

No, a primary key cannot be null. The primary key ensures that every row is uniquely identifiable, and null values would violate this principle by not providing a unique identifier.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)