Mastering Primary and Foreign Keys in SQL for Data Integrity

Imagine running an online store where orders are mixed up - customers receive the wrong items entirely, causing frustration and confusion. Databases need precision to prevent these mistakes. 

Primary and foreign keys are the tools that keep data organized and accurate. Understanding how these keys work is crucial, whether you're joining tables or enforcing relationships between datasets.

This guide explores how to effectively use primary and foreign keys in SQL, from basic definitions to advanced techniques, ensuring you build robust, scalable databases.

What Is a Database Key?

Database keys are essential elements that uniquely identify rows within a table. They play a crucial role in managing data relationships and ensuring the integrity of your data.

Types of Keys

• Primary Keys: Uniquely identify each row in a table. These keys are the backbone of a table and ensure that each record is unique and can be referenced reliably. With primary keys, managing and retrieving data accurately becomes manageable.
• Foreign Keys: Link records between different tables to establish relationships. Foreign keys help enforce referential integrity by ensuring that relationships between tables are consistent. They prevent invalid data from being added by enforcing that any value entered into the foreign key column must exist in the referenced primary key column.
• Unique Keys: Ensure that all values in a column are distinct. Unlike primary keys, which uniquely identify a row, unique keys allow for one column to have unique values, which is useful for attributes like email addresses.
• Composite Keys: Use multiple columns to uniquely identify a record. Composite keys are used when no single attribute can uniquely identify a record, but a combination of attributes can. This is useful in junction tables or tables representing many-to-many relationships.
• Super Keys: Any set of attributes that uniquely identifies a row. Super keys can include multiple attributes, and a primary key is a minimal super key.
• Candidate Keys: Columns that could qualify as a primary key, but only one is chosen as the primary key. Candidate keys are potential options for the primary key, and they all have unique values for each row.

Importance of Keys

Keys help maintain order, consistency, and reliability in your database. They ensure each record is uniquely identifiable and support relationships between tables, which is crucial for complex queries and reporting. Without proper key structures, data redundancy and inconsistency issues can arise, leading to data integrity problems.

Understanding Primary Key in SQL

A primary key in SQL is a column (or a combination of columns) uniquely identifying every table record. It is the main unique identifier, ensuring that no duplicate rows exist. For example, a Product_ID can be used as the primary key in a product table, ensuring each product record is unique. Primary keys are crucial in preventing mix-ups and ensuring every record is distinct.

Primary keys have the following properties:

• Uniqueness: Each value in the primary key column must be unique across all rows.
• Non-null: A primary key cannot have a NULL value because it must reliably identify a record.
• Immutability: Primary keys should remain constant; changing a primary key can lead to integrity issues.

In practice, primary keys are often defined as simple numeric or alphanumeric values, such as auto-incremented IDs, to ensure they are easy to manage and query.

For example, think about online shopping at Amazon. Every product has a unique identifier called Product_ID to ensure there is no mix-up between different products. Imagine ordering a laptop but receiving a toaster instead - this is precisely what primary keys help prevent.

What Is a Foreign Key in SQL?

A foreign key links one table to another by referencing the primary key of the related table. For instance, if you have Products and Categories tables, the Category_ID in the Products table could act as a foreign key linking to the Categories table. Foreign keys help prevent orphaned records and ensure the integrity of relationships between tables.

Foreign keys are essential for:

• Establishing Relationships: They link related data across tables, allowing for efficient joins and accurate queries.
• Referential Integrity: They prevent actions that would leave orphaned records, such as deleting a parent record without addressing the related child records.
• Cascading Actions: Foreign keys can be used with cascading actions (ON DELETE or ON UPDATE) to automatically manage related records when changes occur.

For example, when a customer record is deleted, you can configure the foreign key to automatically delete all orders associated with that customer, thus maintaining data consistency. 

Consider browsing Amazon, where every product belongs to a category, such as books, electronics, or clothing. The Category_ID serves as a foreign key in the Products table to link each product to its respective category in the Categories table, ensuring consistency.

Primary Key vs. Foreign Key in SQL

• Primary Key: Uniquely identifies each row in a table. For example, a Customer_ID uniquely identifies each customer.
• Foreign Key: Establishes a relationship between two tables. For instance, Order_ID in an Order_Details table references Order_ID in the Orders table.

Primary keys and foreign keys work together to maintain database relationships:

• Primary Keys: Ensure that every record is unique within its table.
• Foreign Keys: Enforce referential integrity by linking one table to another.

Practical Example

Consider a scenario where you have Orders and Customers tables:

• The Customer_ID column in Customers acts as a primary key.
• The Customer_ID column in Orders acts as a foreign key linking to Customers.

This relationship ensures that each order is linked to an existing customer, maintaining data integrity. If a customer is deleted, the foreign key constraint ensures that either the related orders are deleted (if ON DELETE CASCADE is used) or the deletion is blocked to prevent orphaned records.

A real-world analogy would be an order placed on an e-commerce website. The Order_ID in the Orders table uniquely identifies each order, while the Customer_ID ensures the order is linked to an existing customer.

💡 Check out our video guide for an in-depth walkthrough of creating and modifying keys in SQL. You can find the link below to follow along visually and reinforce what you learn in this article.

How to Create and Modify Keys with SQL

Understanding how to create and modify primary and foreign keys in SQL is essential for building relational databases that are both robust and easy to manage. Primary keys ensure that each record in a table is uniquely identifiable, while foreign keys create relationships between tables, preserving data consistency. 

In this section, we'll explore step-by-step instructions on defining and managing these keys effectively.

Creating Primary Keys

Primary keys uniquely identify each row in a table and are essential for maintaining data integrity. They are often simple numeric or alphanumeric values designed to prevent duplication and ensure efficient querying.

For example, imagine a “Products” table with columns like Product_ID, Product_Name, and Price. The Product_ID column can serve as the primary key because it uniquely identifies each product.

To define a primary key while creating a table, use the following SQL syntax:

1CREATE TABLE `owox-d-ikrasovytskyi-001.Sales_Data.Product` (
2    Product_ID INT64 NOT NULL,
3    Product_Name STRING NOT NULL,
4    Price NUMERIC,
5    PRIMARY KEY (Product_ID) NOT ENFORCED
6);

‍

‍

‍

Additional Tips for Primary Keys:

• Use AUTO_INCREMENT for numeric primary keys to generate unique values automatically.
• Opt for concise alphanumeric codes (e.g., P001) for easy identification.

Modifying Tables to Add and Remove Primary Key

As databases evolve, you may need to add or remove primary keys from existing tables to adapt to new requirements or resolve inconsistencies.

Adding a Primary Key:

To add a primary key to an existing table, use the following command:

1ALTER TABLE OWOX_Demo.Products
2ADD PRIMARY KEY (Product_ID) NOT ENFORCED;

‍

Note:  Ensure the column intended as the primary key contains unique, non-NULL values before executing this command.

‍

Removing a Primary Key:

To drop an existing primary key constraint:

1ALTER TABLE Products 
2DROP PRIMARY KEY;

‍

Important: Before removing a primary key, ensure it will not disrupt table relationships or integrity.

Creating Foreign Keys

Foreign keys establish relationships between tables and ensure data consistency across linked records. For our example, we are adding a foreign key to both “Products” and “Sellers” table.

To create a foreign key in SQL during table creation:

1CREATE TABLE OWOX_Demo.Products (
2  Product_ID STRING NOT NULL,
3  Product_Name STRING NOT NULL,
4  Category_ID STRING,
5  Price NUMERIC,
6  Seller_ID STRING,
7
8  FOREIGN KEY (Seller_ID) REFERENCES OWOX_Demo.Sellers(Seller_ID) NOT ENFORCED
9);

‍

‍

Best practices for Foreign Keys: Name foreign key constraints (e.g., FK_Products_Seller) for easier management.

Modifying Tables to Add and Remove Foreign Keys

You can adjust relationships between tables by adding or removing foreign keys as needed.

Adding a Foreign Key:

To add a foreign key to an existing table, you can use the following SQL syntax:

1ALTER TABLE OWOX_Demo.Products
2ADD FOREIGN KEY (Seller_ID) REFERENCES OWOX_Demo.Sellers(Seller_ID) NOT ENFORCED;

‍

Removing a Foreign Key:

To drop an existing foreign key constraint, use the following SQL syntax:

1ALTER TABLE Products 
2DROP CONSTRAINT FK_Products_Category;

‍

Ensure no orphaned records remain before dropping a foreign key, as this may compromise data integrity.

Advanced Techniques for Keys in Databases

Keys are the backbone of any database, keeping data accurate and connected. Beyond the basics, techniques like composite keys, surrogate keys, and cascading constraints can handle more complex relationships and keep everything in sync.

Let’s explore how these tools can make your databases smarter and more efficient.

Surrogate Keys vs. Natural Keys

• Surrogate Keys: Typically auto-incremented IDs without business meaning. These are ideal for cases where simplicity and scalability are desired, as they do not change and are easily indexed.
• Natural Keys: Derived from meaningful data, such as a Social Security Number. Natural keys are useful when the identifier has real-world significance, but they may change over time, which can complicate data management.

Use surrogate keys when you need simplicity and scalability, and natural keys when the identifier has business significance. For example, an auto-incremented Customer_ID is a surrogate key, while a National_ID could be a natural key.

Composite Keys for Complex Relationships

Composite keys combine multiple columns to form a unique identifier, useful for defining complex relationships.

Here is how the SQL syntax will look:

1ALTER TABLE OWOX_Demo.Sales
2ADD FOREIGN KEY (Product_ID, Seller_ID)
3REFERENCES OWOX_Demo.Products(Product_ID, Seller_ID) NOT ENFORCED;

‍

Composite keys are especially useful in one-to-many or many-to-many relationships—such as multiple sellers offering various products. By combining Product_ID and Seller_ID into a composite key, we make sure each product-seller pairing is unique and avoid accidental duplicates in the dataset.

How Primary and Foreign Keys Enable Complex Queries: A Real-Life Use Case

Primary and foreign keys establish relationships between tables, enabling efficient retrieval of detailed, structured information critical for decision-making. Let’s explore how these keys power queries through a practical example.

In e-commerce, ensuring accurate data management is critical to maintaining a smooth shopping experience. For instance, when browsing Amazon, you expect every product to have a clear category, a verified seller, and accurate pricing. This seamless coordination is achieved through primary keys and foreign keys, which create structured relationships between database tables.

Let’s explore how Amazon might structure its database to manage products, categories, and sellers effectively. Below are sample datasets for our use case:

Example 1: Creating Relationships with Primary and Foreign Keys

To ensure accurate relationships between products, categories, and sellers, we use foreign keys. Below is how you can define these relationships.

Create the Categories Table:

1CREATE TABLE OWOX_Demo.Categories (
2  Category_ID STRING NOT NULL,
3  Category_Name STRING,
4  PRIMARY KEY (Category_ID) NOT ENFORCED
5);
6
7INSERT INTO OWOX_Demo.Categories (Category_ID, Category_Name)
8VALUES 
9  ('C001', 'Electronics'),
10  ('C002', 'Gadgets'),
11  ('C003', 'Accessories');

‍

‍

‍

Create the Seller Table:

1CREATE TABLE OWOX_Demo.Sellers (
2  Seller_ID STRING NOT NULL,
3  Seller_Name STRING,
4  PRIMARY KEY (Seller_ID) NOT ENFORCED
5);
6
7INSERT INTO OWOX_Demo.Sellers (Seller_ID, Seller_Name)
8VALUES
9  ('S001', 'TechWorld'),
10  ('S002', 'GadgetPro'),
11  ('S003', 'AudioHub'),
12  ('S004', 'SmartGear');

‍

‍

‍

Create the Products Table:

1CREATE TABLE OWOX_Demo.Products (
2  Product_ID STRING NOT NULL,
3  Product_Name STRING,
4  Category_ID STRING,
5  Price NUMERIC,
6  Seller_ID STRING,
7  PRIMARY KEY (Product_ID) NOT ENFORCED,
8  FOREIGN KEY (Category_ID) REFERENCES OWOX_Demo.Categories(Category_ID) NOT ENFORCED,
9  FOREIGN KEY (Seller_ID) REFERENCES OWOX_Demo.Sellers(Seller_ID) NOT ENFORCED
10);
11
12INSERT INTO OWOX_Demo.Products (Product_ID, Product_Name, Category_ID, Price, Seller_ID)
13VALUES
14  ('P001', 'Laptop', 'C001', 1200, 'S001'),
15  ('P002', 'Smartphone', 'C002', 800, 'S002'),
16  ('P003', 'Tablet', 'C001', 500, 'S001'),
17  ('P004', 'Headphones', 'C003', 150, 'S003'),
18  ('P005', 'Smartwatch', 'C002', 300, 'S004');

‍

‍

Example 2: Querying Data Using Joins

Now that the relationships are established, let’s see how to query the database to gather meaningful insights. For example, to fetch a list of products along with their category names and seller details.

1SELECT 
2  Products.Product_ID, 
3  Products.Product_Name, 
4  Categories.Category_Name, 
5  Products.Price, 
6  Sellers.Seller_Name
7FROM 
8  OWOX_Demo.Products
9JOIN 
10  OWOX_Demo.Categories ON Products.Category_ID = Categories.Category_ID
11JOIN 
12  OWOX_Demo.Sellers ON Products.Seller_ID = Sellers.Seller_ID;

‍

The query combines data from the Products, Categories, and Sellers tables to provide a consolidated view of the product catalog, including category names and seller details.

The output would look like the following:

Example 3: Querying Products by Category

Suppose a category manager wants to fetch all products under the "Electronics" category to analyze their performance.

The SQL Query will look like the following:

1SELECT 
2  Products.Product_ID, 
3  Products.Product_Name, 
4  Products.Price 
5FROM 
6  OWOX_Demo.Products
7JOIN 
8  OWOX_Demo.Categories ON Products.Category_ID = Categories.Category_ID
9WHERE 
10  Categories.Category_Name = 'Electronics';

‍

The output would look like the following:

Example 4: Ensuring Data Integrity with Foreign Keys

Imagine adding a new product, “Wireless Keyboard,” to the Products table and assigning it a Category_ID like C999 that doesn’t exist in the Categories table. Without safeguards, this could create errors in your data.

Use the following SQL Query to avoid such errors:

1ALTER TABLE OWOX_Demo.Products
2ADD FOREIGN KEY (Category_ID)
3REFERENCES OWOX_Demo.Categories(Category_ID) NOT ENFORCED;

‍

The database will reject invalid entries, ensuring every product is linked to an existing category. This keeps your data accurate and prevents mistakes like assigning products to non-existent categories.

Example 5: Handling Cascading Deletes

Suppose you delete a category, like "Accessories," from the Categories table. Without additional rules, the associated products in the Products table would remain, creating orphaned records.

To prevent this, you can use cascading deletes, in BigQuery Cascading isn't supported, so we will be using the manual method:

1-- Manual cascading delete
2DELETE FROM OWOX_Demo.Products
3WHERE Category_ID IN (
4  SELECT Category_ID
5  FROM OWOX_Demo.Categories
6  WHERE Category_Name = 'Accessories'
7);
8
9-- Then delete the category
10DELETE FROM OWOX_Demo.Categories
11WHERE Category_Name = 'Accessories';

‍

With this

Deleting a category automatically removes all related products, ensuring your database stays clean and consistent. For example, deleting the "Accessories" category will also remove products like "Headphones" tied to it.

Example 6: Combining Data Across Multiple Tables with Joins

Suppose a store manager needs a report showing all products, their categories, and seller details in one view. 

To achieve this, we can use a JOIN query:

1SELECT 
2  Products.Product_ID, 
3  Products.Product_Name, 
4  Categories.Category_Name, 
5  Sellers.Seller_Name 
6FROM 
7  OWOX_Demo.Products
8JOIN 
9  OWOX_Demo.Categories ON Products.Category_ID = Categories.Category_ID
10JOIN 
11  OWOX_Demo.Sellers ON Products.Seller_ID = Sellers.Seller_ID;

‍

This query retrieves a consolidated report that combines information from the Products, Categories, and Sellers tables. It links each product to its category and seller, providing a complete overview of the inventory. 

The output will look like the following:

Example 7: Validating Foreign Key Constraints

Let’s say you try to add a product to the Products table with a Seller_ID like 'S998' that doesn’t exist in the Sellers table. In traditional databases, a foreign key constraint would block this insert to maintain data integrity.

However, in BigQuery, foreign keys are not enforced, meaning the database will allow the insert even if 'S998' is not present in the Sellers table.

To simulate this rule and prevent invalid data, you can use a conditional INSERT that checks if the seller exists:

1INSERT INTO OWOX_Demo.Products (Product_ID, Product_Name, Category_ID, Price, Seller_ID)
2SELECT 'P006', 'Camera', 'C001', 700, 'S999'
3FROM UNNEST([1])  
4WHERE EXISTS (
5  SELECT 1 FROM OWOX_Demo.Sellers WHERE Seller_ID = 'S998'
6);

‍

This approach ensures that only valid Seller_IDs are inserted into the Products table, helping maintain consistency without enforced constraints.

Example 8: Optimizing Queries with Indexed Primary Keys

In traditional databases, creating indexes on primary key columns is a common way to speed up search and join operations. However, BigQuery doesn’t support CREATE INDEX or enforce primary key constraints. Instead, it uses automatic optimization and offers alternative methods to improve query performance.

Here is a query sample for BigQuery:

1CREATE TABLE OWOX_Demo.Products1 (
2  Product_ID STRING NOT NULL,
3  Product_Name STRING,
4  Category_ID STRING,
5  Price NUMERIC,
6  Seller_ID STRING,
7
8  PRIMARY KEY (Product_ID) NOT ENFORCED,
9  FOREIGN KEY (Category_ID) REFERENCES OWOX_Demo.Categories(Category_ID) NOT ENFORCED,
10  FOREIGN KEY (Seller_ID) REFERENCES OWOX_Demo.Sellers(Seller_ID) NOT ENFORCED
11)
12CLUSTER BY Product_ID;

‍

In BigQuery, traditional indexing isn’t supported. Instead, you can use clustering to improve query performance. Declaring a PRIMARY KEY is for metadata only, while clustering organizes data to speed up queries that filter by specific columns like Product_ID.

This index speeds up queries involving the Product_ID column, improving search, update, and join performance.

Mistakes to Avoid When Working with Keys

When working with primary and foreign keys in SQL, several common mistakes can compromise your database's integrity, performance, and reliability. Understanding and avoiding these pitfalls can help you create a more robust and efficient database structure. Below, we discuss some of the key mistakes and how to address them.

Not Setting Primary Keys

Every table should have a primary key to maintain organized and searchable data. A primary key makes it easier to identify records, leading to duplicate data and inconsistencies.

Using Inappropriate Fields as Primary Keys

Avoid the use of changeable fields like phone numbers or names as primary keys. These fields can change over time, leading to data consistency issues and difficulty maintaining relationships between tables. Instead, use stable identifiers like auto-incremented numeric IDs.

For example, using Product_Name as a primary key in a Products table would be inappropriate since multiple sellers could sell the same product, making it non-unique. Instead, use Product_ID, which is unique and remains constant.

Overusing Foreign Keys

Limit the use of foreign keys to prevent overly complex database schemas. Too many foreign keys can lead to performance bottlenecks, especially during inserts, updates, and deletes, as the database must enforce referential integrity.

Ignoring Cascade Rules

Cascade rules help maintain consistency during updates or deletions in related tables. Without proper cascading actions (ON DELETE CASCADE or ON UPDATE CASCADE), orphaned records may occur, leading to data integrity issues.

Poor Indexing

Ensure primary and foreign keys are indexed to enhance query performance. Indexing these keys allows the database to locate records quickly during join operations, significantly improving query efficiency, especially for large datasets.

Best Practices for Primary and Foreign Keys

Effectively working with primary and foreign keys is crucial to maintaining your database's integrity, performance, and reliability. Following best practices helps prevent common issues, ensures efficient data retrieval, and smooths the overall database management process.

Define Foreign Keys to Ensure Referential Integrity

Foreign keys help maintain data accuracy by ensuring consistent relationships between tables. They link records across tables, preventing orphaned records and enforcing referential integrity. Always define foreign keys to maintain these connections and ensure that related data stays valid.

Optimize Performance by Indexing Foreign Keys

Indexing foreign key columns can greatly enhance query speed by allowing the database to locate related rows quickly. This is particularly useful for join operations and large datasets, improving overall performance and efficiency in data retrieval.

Choose Appropriate ON DELETE/ON UPDATE Actions

Use cascading actions carefully to maintain data integrity. ON DELETE CASCADE can delete child records when a parent record is removed, ensuring referential consistency. Choose cascading options that align with your data management needs to avoid unintended deletions or updates.

Avoid Circular References in Foreign Keys

Avoid circular references, as they complicate database logic and can lead to data consistency issues. They may also create deadlocks during complex transactions, making maintenance difficult. It's best to design tables to eliminate such references whenever possible.

Balance Performance and Data Integrity with Foreign Keys

Use foreign keys where they add value without impacting performance. In large-scale distributed databases, weigh the benefits of enforcing foreign key constraints against the potential performance trade-offs.

Handle NULL Values in Foreign Keys Thoughtfully

Foreign keys can have NULL values if the relationship is optional, but overuse can complicate queries. Manage NULL values carefully to avoid unintended results and maintain data integrity.

Apply Cascading Actions Carefully

Cascading actions such as ON DELETE CASCADE or ON UPDATE CASCADE should be applied thoughtfully to ensure that changes in parent tables do not unintendedly cause data loss in child tables.

Use Foreign Keys Strategically in Distributed Databases

Foreign keys can add complexity to distributed databases due to data residing in multiple locations. To simplify management, consider using application-level validation for referential integrity rather than enforcing foreign key constraints at the database level.

Leverage Deferred Constraint Checking for Complex Transactions

Deferred constraint checking validates constraints at the end of a transaction rather than immediately. This is useful when multiple interdependent updates are needed. It ensures that all operations are completed before enforcing constraints, thereby maintaining data integrity.

Document Foreign Key Relationships Clearly

Document foreign key relationships to help developers and analysts understand table dependencies. Clear documentation makes it easier to maintain and modify the database schema, ensuring smooth collaboration and reducing the risk of errors during changes.

Plan for Schema Changes Affecting Foreign Keys

When modifying a table referenced by a foreign key, update all dependent tables and relationships accordingly. This helps maintain data integrity and prevents issues arising from inconsistent references.

Test Foreign Key Constraints Before Deployment

Test foreign key constraints in a development environment before deploying to production. This helps identify data integrity issues early, ensuring that relationships between tables are correctly enforced before reaching the production stage.

Make Data Reporting Easy with the OWOX Reports Extension for Sheets

Streamline your data reporting and analysis with the OWOX Reports. Effortlessly generate detailed reports, charts, and pivots by connecting your Google Sheets directly to BigQuery and other data sources. Simplify complex queries, visualize your data, and make informed decisions with just a few clicks. Install the OWOX Reports to save time and elevate your data management process.