Difference Between Primary Key and Foreign Key

Primary and foreign keys are essential for linking tables in a relational database. A primary key uniquely identifies each record in a table, while a foreign key refers to the primary key in another table to create a relationship. Though they serve different roles, they work together to maintain data integrity and structure. Proper use of these keys helps to organize the database and improve both performance and scalability. In this blog, you will learn what primary and foreign keys are, their differences, and real-world use cases.

Table of Contents:

• What are Keys in SQL?
• What is a Primary Key in SQL?
• What is a Foreign Key in SQL?
• Properties for Primary and Foreign Keys in SQL
• Relationship Between Primary Key and Foreign Key in SQL
• Difference Between Primary Key and Foreign Key in SQL
• Common Mistakes with Primary and Foreign Keys in SQL
• Best Practices for Using Primary and Foreign Keys
• Real-World Use Case of Primary and Foreign Keys
• Conclusion

What are Keys in SQL?

Keys in SQL play a crucial role in organizing data within a database. They help keep the data structured and consistent. Without keys, it would be difficult to manage data accurately and avoid duplicates or mismatched records.

Here are some key reasons why SQL keys are important:

1. Uniqueness: Keys help in making sure that each record in a table is unique by preventing data duplication and helping in identifying the record easily.

2. Relationships Between Tables: Keys help create relationships between tables, which enables the databases to understand and maintain structural integrity.

3. Data Integrity:  Keys are important in maintaining the integrity and consistency of data by making sure that there is no invalid or incorrect data entered into the table.

4. Faster Searching and Filtering: Using keys properly will enable the database to search and filter the data much more quickly. This will improve the speed of queries and overall performance of the database, especially with large volumes of data. This is important to save time and ensure database operations are efficient.

Master SQL & Elevate Your Career

Get lifetime access to expert-led lessons, real-world projects, and career-ready skills. Enroll now and start your data journey!"

Explore Program

What is a Primary Key in SQL?

A primary key is a unique identifier that ensures there are no duplicate values or NULL values in the column. It helps to identify each record in the table clearly and accurately. A table can only have one primary key. Primary keys are important for keeping the data clean, organized, and easy to manage.

Syntax:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype
);

Let’s understand the working of the primary key with an example.

Example:

-- Creating a table
CREATE TABLE intellipaat_courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);
-- Inserting Data
INSERT INTO intellipaat_courses (course_id, course_name, category, price) VALUES
    (101, 'Data Science Master Program', 'Data Science', 899.99),
    (102, 'Full Stack Web Development', 'Web Development', 749.99),
    (103, 'AWS Solutions Architect', 'Cloud Computing', 599.99);
SELECT * FROM intellipaat_courses;

Output:

Explanation: Here, in this query, course_id is used as the primary key, which makes sure that each course that is offered by intellipaat has a unique identification number.

What is a Foreign Key in SQL?

A foreign key in SQL is used to establish a relationship between a column in one table and the primary key of another table. It ensures referential integrity by preventing actions that would result in orphaned records.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    FOREIGN KEY (column_name) REFERENCES other_table(other_column)
);

Let’s understand the working of a foreign key with an example:

Example:

CREATE TABLE owners (
    owner_id INT PRIMARY KEY,
    owner_name VARCHAR(100),
    car_id INT REFERENCES cars(car_id)

);
-- Inserting Data
INSERT INTO owners (owner_id, owner_name, car_id) VALUES
    (1, 'Alice Johnson', 1),
    (2, 'Bob Smith', 2),
    (3, 'Charlie Brown', 3);
SELECT* FROM owners;

Output:

Explanation: Here, in this query, owner_id is set as the primary key, while owner_name is a regular column, and car_id acts as a foreign key that refers to car_id in the cars table.

Properties for Primary and Foreign Keys in SQL

Relationship Between Primary Key and Foreign Key in SQL

The primary key and foreign key work together to relate two tables and maintain data integrity.

Let us break down the steps:

• The primary key is defined in the main table, often called the parent table. It uniquely identifies each record in that table.
• The foreign key is defined in another table, known as the child table, and it refers to the primary key in the parent table.
• This connection ensures that every value in the foreign key column matches a valid primary key in the parent table, which helps maintain proper relationships between the tables and prevents invalid or orphaned records.

Difference Between Primary Key and Foreign Key in SQL

Common Mistakes with Primary and Foreign Keys in SQL

Let’s look at some common mistakes that need to be taken care of while working with primary keys and foreign keys in SQL.

1. Allowing NULL or Duplicate Values in a Primary Key: One of the major mistakes is allowing NULL or duplicate values in a primary key column. There should always be a unique value in a primary key, and these values cannot be empty. If you allow duplicates or leave the value blank, the database may fail to identify each row correctly, which can lead to confusion and data errors.

2. Foreign Key Columns Missing an Index: Foreign key columns are often used in JOIN operations between two tables. Without an index on the foreign key column, the database will take longer to perform searches and match records, especially with larger tables. This results in poor performance and reduced efficiency. An index on a foreign key column would help the query run faster and without delays.

3. Attempting to Insert Foreign Key Values not exist in the Parent Table: Foreign keys must refer to existing values in the Parent Table. If you attempt to insert a value in the child table that does not exist in the parent table, you will receive an error. This violates the relationship in the tables, which can create corrupted data as a result. Always ensure the value you insert as a foreign key already exists in the parent table.

4. Ignoring the ON DELETE or ON UPDATE Rules: When you delete or update a parent record, you must consider what happens to the related records in the child table. If you do not define an action, such as an ON DELETE CASCADE or ON UPDATE CASCADE, you might end up with child records without a matching parent, resulting in broken links. By defining proper rules, you ensure correct and meaningful information.

Best Practices for Using Primary and Foreign Keys

Let us look at some best practices that we can follow while working with primary key and foreign key:

1. Always Provide a Primary Key for Every Table: Every table should have a primary key to uniquely identify all records of a table. This can help prevent records from being duplicated or lost, and searching, updating, and deleting records is more accurate.

2. Use Surrogate Keys Instead of Real-World Values: Instead of a real-world value like a name or an email, it is better to use something like a system-generated ID, like StudentID. Surrogate keys are stable, always uniquely identify a record, and are less likely to change.

3. Ensure Data Types Match Between Primary and Foreign Keys: Ensure the foreign key has the same data type and size as the related primary key. If they don’t match, the database will not enforce the foreign key constraint, will read correctly, and may fail to enforce the intended relationship or constraint.

4. Create Indexes on Foreign Keys: Adding indexes on foreign keys can help the database locate and join related rows faster, which is useful for extensive or complicated tables.

5. Use ON DELETE and UPDATE Options: Always declare what will happen with the parent data if it is deleted or updated! This is also a good use of constraints like CASCADE or SET NULL, and allows data to remain whole and accurate concerning the linked tables.

Real-World Use Case of Primary and Foreign Keys

1. Online Bookstore System

An online bookstore is used to keep track of customer details and book orders. 

-- Creating the customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
-- Creating the books table
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    book_title VARCHAR(100)

);
-- Creating the orders table with foreign keys
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    book_id INT REFERENCES books(book_id)
);
-- Inserting data into the customers table
INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Alice Johnson'),
    (2, 'Bob Williams');
-- Inserting data into the books table
INSERT INTO books (book_id, book_title) VALUES
    (201, 'Learn SQL Basics'),
    (202, 'Advanced Web Design');
-- Inserting data into the orders table
INSERT INTO orders (order_id, customer_id, book_id) VALUES
    (1, 1, 201),
    (2, 2, 202);
-- Displaying results
SELECT * FROM customers;
SELECT * FROM books;
SELECT * FROM orders;

Output:

Explanation: In this example, customer_id in the customers table and book_id in the books table are acting as primary keys, which helps to uniquely identify each record. The orders table uses its primary key, which is order_id, and also includes a foreign key to connect customers with their books.

Learn SQL for Free – Start Today!

No sign-up fees. Just pure, practical SQL skills to kickstart your data journey.

Explore Program

Conclusion

Using primary keys and foreign keys properly is essential for creating a well-organized and reliable database. Primary keys ensure that each record is unique, while foreign keys maintain meaningful connections between related tables. Together, they prevent common errors, support data consistency, and improve the overall performance and reliability of the system. These keys form the foundation of a clean, accurate, and efficient database.

Take your skills to the next level by enrolling in the SQL Course today and gain hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

Difference Between Primary Key and Foreign Key – FAQs