Editorial Team · on 13 June 2026 · 11 min read · Last reviewed 13 June 2026

SQL and relational database tutorials provide structured guidance on designing, managing, and querying relational databases using SQL.

Key facts

• Primary keys uniquely identify records in a table
• Foreign keys establish relationships between tables
• Together, they ensure data integrity and enable efficient queries
• Understanding these concepts is crucial for effective database design

What is a primary key?

A primary key is a column or combination of columns that uniquely identifies each row in a table. It ensures that no two rows have the same primary key value, and it cannot contain NULL values. Primary keys are fundamental to database design because they enforce entity integrity, meaning each record in the table is uniquely identifiable.

For example, in a table called employees, the employee_id column might serve as the primary key. Each employee would have a unique ID, allowing the database to distinguish between different employees easily. Primary keys are often used as reference points for foreign keys in other tables, establishing relationships between different datasets.

When designing a database, choosing the right primary key is crucial. Natural keys, which are derived from real-world attributes like Social Security numbers, are sometimes used. However, surrogate keys, such as auto-incrementing integers, are often preferred because they are simple and independent of the data’s semantics.

What is a foreign key?

A foreign key is a column or combination of columns in one table that references the primary key of another table. Foreign keys create relationships between tables, enabling the database to maintain referential integrity. This means that the values in the foreign key column must correspond to values in the referenced primary key column, ensuring that relationships between tables are valid.

For instance, consider a table called orders that includes a column customer_id. This column would be a foreign key that references the customer_id primary key in a customers table. This relationship allows the database to link each order to a specific customer, ensuring that the customer exists in the database.

Foreign keys also help prevent orphaned records, which are records in one table that have no corresponding records in the related table. By enforcing referential integrity, foreign keys ensure that the database remains consistent and that queries return accurate results.

How do primary and foreign keys work together?

Primary and foreign keys work together to establish relationships between tables, creating a structured and interconnected database. The primary key in one table acts as a reference point, while the foreign key in another table points back to this reference. This relationship allows for efficient querying and data manipulation across multiple tables.

For example, consider a database for a library. The books table might have a primary key book_id, and the loans table might have a foreign key book_id that references the books table. This relationship allows the database to track which books are loaned out and to whom. When a query is executed to find all loans for a specific book, the database uses the foreign key to look up the corresponding primary key in the books table.

Together, primary and foreign keys enable complex queries that involve multiple tables. They allow for joins, which combine rows from two or more tables based on related columns. This capability is essential for data analysis and reporting, as it allows users to extract meaningful insights from the database.

Best practices for using primary and foreign keys

When designing a database, it is important to follow best practices for using primary and foreign keys. One key practice is to choose primary keys that are unique, non-NULL, and stable over time. Surrogate keys, such as auto-incrementing integers, are often a good choice because they are simple and independent of the data’s semantics.

Another best practice is to ensure that foreign keys are properly defined and enforced. This means that the values in the foreign key column must correspond to values in the referenced primary key column. Additionally, foreign keys should be indexed to improve query performance. Indexes allow the database to quickly locate the rows that match a specific foreign key value, reducing the time required to execute queries.

It is also important to consider the type of relationship between tables when defining primary and foreign keys. One-to-one relationships involve a single record in one table being linked to a single record in another table. One-to-many relationships involve a single record in one table being linked to multiple records in another table. Many-to-many relationships involve multiple records in one table being linked to multiple records in another table. Understanding these relationships is crucial for designing an effective database schema.

Common mistakes to avoid

When working with primary and foreign keys, there are several common mistakes to avoid. One mistake is using composite keys, which are primary keys made up of multiple columns, unnecessarily. Composite keys can make the database design more complex and can lead to performance issues. It is often better to use a single-column primary key, such as an auto-incrementing integer, unless there is a specific reason to use a composite key.

Another mistake is failing to enforce referential integrity. This can lead to orphaned records, which are records in one table that have no corresponding records in the related table. To avoid this, it is important to define foreign keys properly and to ensure that the database enforces referential integrity. This can be done using constraints, such as ON DELETE CASCADE or ON DELETE SET NULL, which automatically handle the deletion of related records.

It is also important to avoid using NULL values in primary or foreign key columns. NULL values indicate the absence of a value, and they can lead to data integrity issues. For example, if a foreign key column contains a NULL value, it means that the record is not linked to any record in the related table. To avoid this, it is important to ensure that primary and foreign key columns are defined as NOT NULL.

Types of keys and their uses

In addition to primary and foreign keys, there are several other types of keys that are used in database design. Composite keys, as mentioned earlier, are primary keys made up of multiple columns. These are used when a single column is not sufficient to uniquely identify a record. For example, in a table that tracks student enrollment in courses, a composite key might consist of the student ID and the course ID.

Unique keys are similar to primary keys, but they allow for NULL values and do not enforce referential integrity. Unique keys are used to ensure that a specific column or combination of columns contains only unique values. For example, in a table called employees, the email column might be defined as a unique key to ensure that each employee has a unique email address.

Surrogate keys are artificial keys that are used as primary keys. They are often auto-incrementing integers or GUIDs (Globally Unique Identifiers). Surrogate keys are used when there is no natural key that is unique, non-NULL, and stable over time. For example, in a table called products, a surrogate key might be used as the primary key instead of the product name, which might change over time.

Querying with primary and foreign keys

Primary and foreign keys enable efficient querying across multiple tables. One common way to query with primary and foreign keys is to use joins. Joins combine rows from two or more tables based on related columns. For example, an INNER JOIN returns only the rows that have matching values in both tables. An LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table.

For instance, consider the following query that retrieves all orders for a specific customer:

Customer ID	Customer Name	Order ID	Order Date
1	John Doe	101	2023-01-15
1	John Doe	102	2023-02-20
2	Jane Smith	103	2023-03-10

The query might look like this:

SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 1;

This query uses an INNER JOIN to combine rows from the customers and orders tables based on the customer_id column, which is the primary key in the customers table and the foreign key in the orders table.

Performance considerations

When designing a database, it is important to consider the performance implications of primary and foreign keys. One key consideration is indexing. Indexes are data structures that improve the speed of data retrieval operations. By default, primary keys are indexed, which allows the database to quickly locate the rows that match a specific primary key value. Foreign keys should also be indexed to improve query performance.

Another consideration is the size of the primary and foreign key columns. Smaller columns, such as integers, are generally faster to index and retrieve than larger columns, such as strings. This is because smaller columns require less storage space and can be processed more quickly by the database. However, it is important to choose primary and foreign key columns that are unique, non-NULL, and stable over time, even if this means using larger columns.

The number of rows in the table can also impact performance. As the number of rows increases, the time required to execute queries also increases. This is because the database must search through more rows to find the matching values. To mitigate this, it is important to design the database schema carefully, choosing appropriate primary and foreign keys and indexing columns that are frequently queried.

Steps for implementing primary and foreign keys

1. Identify the columns that will serve as primary keys in each table. These columns should be unique, non-NULL, and stable over time.

2. Define the primary keys in each table using the PRIMARY KEY constraint. For example:

   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50)
   );
   

3. Identify the columns that will serve as foreign keys in each table. These columns should reference the primary keys of other tables.

4. Define the foreign keys in each table using the FOREIGN KEY constraint. For example:

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );
   

5. Ensure that the database enforces referential integrity. This can be done using constraints, such as ON DELETE CASCADE or ON DELETE SET NULL, which automatically handle the deletion of related records.

6. Index the foreign key columns to improve query performance. This can be done using the CREATE INDEX statement. For example:

   CREATE INDEX idx_orders_customer_id ON orders(customer_id);
   

Comparing primary and foreign keys

While primary and foreign keys serve different purposes in database design, they are closely related and work together to establish relationships between tables. Here is a comparison of their key characteristics:

Characteristic	Primary Key	Foreign Key
Purpose	Uniquely identifies records in a table	Establishes relationships between tables
Uniqueness	Must be unique	Must correspond to a value in the referenced primary key column
NULL values	Cannot contain NULL values	Can contain NULL values, but typically should not
Indexing	Automatically indexed	Should be indexed for performance
Constraints	Defined using the PRIMARY KEY constraint	Defined using the FOREIGN KEY constraint

Real-world example

Consider a database for an e-commerce platform. The database might include tables for customers, products, orders, and order items. In this scenario, the customers table might have a primary key customer_id, and the orders table might have a foreign key customer_id that references the customers table. The products table might have a primary key product_id, and the order_items table might have foreign keys order_id and product_id that reference the orders and products tables, respectively.

This database design allows the e-commerce platform to track customer orders, including the products purchased and the quantities ordered. It also enables efficient querying across multiple tables, such as retrieving all orders for a specific customer or all products in a specific order. By using primary and foreign keys, the database ensures data integrity and enables complex queries that involve multiple tables.

Practical takeaway

Understanding the differences between primary and foreign keys and how they establish relationships between tables is crucial for effective database design. By following best practices, such as choosing appropriate primary and foreign keys, enforcing referential integrity, and indexing columns that are frequently queried, you can create a structured and interconnected database that supports efficient querying and data analysis. Additionally, understanding the types of relationships between tables and the types of keys that are used in database design can help you create a database schema that meets the specific needs of your application.

Frequently asked questions

What is a primary key?

A primary key uniquely identifies each record in a table. It ensures no duplicate entries and provides a consistent way to reference data. For example, in an 'Employees' table, the 'EmployeeID' could be the primary key, ensuring each employee has a unique identifier.

What is a foreign key?

A foreign key creates a link between data in two tables. It references the primary key of another table, establishing relationships. For instance, in an 'Orders' table, 'EmployeeID' could be a foreign key linking to the 'Employees' table, showing which employee processed each order.

How do primary and foreign keys work together?

Primary and foreign keys work together to maintain data integrity. The primary key ensures unique identification in its table, while the foreign key references this primary key to create a relationship. This linkage allows for efficient queries and data consistency across tables.

Can a foreign key be a primary key?

Yes, a foreign key can also be a primary key in another table. This is common in junction tables used for many-to-many relationships. For example, a 'StudentCourses' table might have 'StudentID' and 'CourseID' as primary keys, both of which are foreign keys referencing primary keys in 'Students' and 'Courses' tables.