Foreign Keys Explained: Essential Concepts and Best Practices

Join StarRocks Community on Slack

Connect on Slack

TABLE OF CONTENTS

See All Glossary Items

Primary Key

Composite Keys

Hierarchical Database

Best Practices for Database Schema Design

Create, Read, Update, and Delete (CRUD)

Publish date: Aug 26, 2024 4:30:09 PM

What Are Foreign Keys?

Foreign keys are a critical part of relational databases that help maintain relationships between tables. Specifically, a foreign key is a field (or collection of fields) in one table that refers to the primary key in another table. It ensures that the values in the foreign key column correspond to actual values in the related table, thus enforcing referential integrity. Foreign keys create a strong link between the tables, particularly when you need to manage dependencies between records.

For example, if you have a students table and a classes table, the class_id in the students table can be a foreign key that points to the id of a class in the classes table. This way, every student is linked to an existing class, and the system ensures that a class cannot be deleted if there are students still assigned to it.

How Foreign Keys Differ from Primary Keys

Primary Key: A primary key is a column (or combination of columns) that uniquely identifies each record in a table. There can be only one primary key per table, and it cannot contain null values.
Foreign Key: A foreign key is a column (or set of columns) that links a row in one table to a row in another table, referencing the primary key of the other table. A table can have multiple foreign keys, and foreign keys can have null values (unless specifically constrained).

Purpose of Foreign Keys

The purpose of foreign keys is to create a connection between two tables that ensures the data remains accurate and consistent across your database. Here’s how foreign keys help:

Maintain Consistency
A foreign key ensures that the values in one table correspond to valid entries in another table, preventing data inconsistencies.
Example: Suppose you have a students table and a classes table. In the students table, the column class_id is a foreign key referencing the class_id in the classes table. This setup ensures that any student must be assigned to a valid class, preventing the insertion of invalid class IDs into the students table.
Prevent Invalid Data
Foreign keys help block the insertion of values in the child table that don’t match any existing value in the parent table. This keeps your database free from invalid records.
Example: If someone tries to add a student with class_id = 10, but there’s no class with class_id = 10 in the classes table, the system will reject the entry. This ensures students can only belong to existing classes.
Enable Cascading Actions
Foreign keys allow you to set up cascading actions, which means that changes in the parent table automatically apply to the related records in the child table.
Example: If you delete a class from the classes table, you can set up cascading delete actions to remove all the students linked to that class in the students table. Similarly, cascading updates can ensure that if a class_id changes in the classes table, the corresponding class_id is also updated in the students table automatically.

Importance of Foreign Keys

Foreign keys are crucial in any database design because they enforce relationships and help maintain clean and organized data. Let’s break down their importance:

Data Integrity
Foreign keys ensure that your data remains consistent by linking records in different tables logically.
Example: In a scenario where students belong to classes, the foreign key linking students.class_id to classes.class_id ensures no student can be linked to a non-existent class. This prevents "orphaned" students without valid classes, maintaining the integrity of the data.
Efficient Data Management
Managing data dependencies becomes easier with foreign keys because they allow you to delete or update parent records while ensuring that related child records are handled properly.
Example: When you delete a row from the classes table, cascading actions can automatically delete all students enrolled in that class. This saves time and prevents the need for manually cleaning up related records, making the database easier to manage.
Normalization
Foreign keys support database normalization by allowing you to divide data into logical, smaller tables, reducing redundancy.
Example: Instead of storing the class name directly in the students table for every student, you store it once in the classes table and use the class_id as a foreign key in the students table. This not only reduces data duplication but also makes updates easier. If the class name changes, you only update it in one place, rather than across multiple records.
Referential Integrity
Foreign keys enforce referential integrity, ensuring that relationships between tables remain valid and correct.
Example: In the students and classes relationship, foreign keys ensure that every student points to a valid class. If a class is deleted, the system either prevents the deletion or cascades the change to keep the database consistent.

Technical Aspects of Foreign Keys

Foreign keys are a fundamental feature in relational databases that enforce relationships between tables. While their primary purpose is to maintain referential integrity, there are several technical aspects to consider when using foreign keys. These aspects touch on how foreign keys are implemented, enforced, and utilized in database systems.

Definition of Foreign Keys

A foreign key is a column or combination of columns in a table that links to the primary key or a unique key in another table. This establishes a relationship between the two tables, enforcing consistency across related data.

SQL Syntax Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

Referential Integrity

Foreign keys enforce referential integrity in relational databases. This means that the values in a foreign key column must match existing values in the parent table’s primary or unique key column. If a reference is broken (e.g., if a record in the parent table is deleted while a child record still exists), it could result in "orphaned" rows, which the foreign key constraint prevents.

Key Concepts:
- Parent table: The table that contains the primary or unique key being referenced.
- Child table: The table that contains the foreign key linking back to the parent table.

Foreign Key Constraints

Foreign key constraints ensure that relationships between tables remain consistent. When you define a foreign key, you can also specify the behavior when records in the parent table are updated or deleted, using actions such as:

ON DELETE CASCADE: Deletes all related rows in the child table when a row in the parent table is deleted.
ON DELETE SET NULL: Sets the foreign key column in the child table to NULL when the corresponding parent record is deleted.
ON DELETE RESTRICT: Prevents the deletion of a parent row if it has related child rows.
ON UPDATE CASCADE: Automatically updates related rows in the child table when the primary key of the parent table is updated.

Example of a Foreign Key with CASCADE:

ALTER TABLE students
ADD CONSTRAINT fk_class_student
FOREIGN KEY (class_id) REFERENCES classes(class_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Indexing of Foreign Keys

Foreign keys are not automatically indexed, although primary keys are. To improve the performance of queries involving foreign keys, especially in large databases, it is common practice to create an index on the foreign key column(s). Without indexing, queries that involve joins between parent and child tables can result in poor performance as the database will perform full table scans.

SQL to Create an Index on a Foreign Key:

CREATE INDEX idx_students_class_id
ON students(class_id);

Constraints Checking

When foreign keys are enforced, the database system checks the foreign key constraint during INSERT, UPDATE, or DELETE operations to ensure data consistency. The constraint verification can have an impact on database performance:

INSERT Operation: Before a new record is inserted into the child table, the database checks if the corresponding key exists in the parent table.
DELETE Operation: When a row is deleted from the parent table, the system checks if there are dependent rows in the child table and behaves according to the ON DELETE action specified.
UPDATE Operation: The system checks the consistency of foreign key values whenever a foreign key or referenced primary key is updated.

Cascading Actions

Cascading actions are an advanced feature of foreign keys that help automate changes to related records. These actions are useful when parent-child relationships require automatic updates or deletions in the child table based on changes to the parent table.

Cascade Update: If the value of a primary key in the parent table changes, all foreign key values in the related child table are automatically updated.
Cascade Delete: If a record is deleted in the parent table, all records in the child table that reference that primary key are automatically deleted.

SQL Example:

ALTER TABLE orders
ADD CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

Nullability of Foreign Keys

Foreign key columns can accept NULL values unless explicitly defined otherwise. A NULL value in a foreign key column means that the record in the child table is not related to any record in the parent table. This can be useful in cases where the relationship is optional.

Example: If a student is not yet assigned to a class, the class_id field in the students table can be set to NULL.

SQL Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    class_id INT NULL,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

Self-Referencing Foreign Keys

In some cases, a table can have a foreign key that references itself. This is called a self-referencing foreign key and is useful for hierarchical data structures such as employee-supervisor relationships.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Deferred Constraint Checking

Some databases (e.g., PostgreSQL, Oracle) allow for deferred constraint checking, where foreign key constraints are only checked at the end of a transaction, instead of immediately during an individual operation. This allows for more flexibility, especially during complex multi-step processes that might involve temporarily breaking foreign key constraints during the transaction.

SQL Example in PostgreSQL:
```
SET CONSTRAINTS ALL DEFERRED;
```

Performance Considerations

While foreign keys are vital for maintaining data integrity, they can have a performance impact in some scenarios:

Insert/Update Overhead: Each insert or update operation on a foreign key involves checking the referenced table, which adds some overhead, especially in high-transaction environments.
Join Performance: When performing JOINs between the parent and child tables, the absence of indexing on the foreign key columns can lead to slow query performance.

Foreign Keys in Distributed Databases

In distributed or sharded database systems, enforcing foreign key constraints becomes complex. Many distributed databases like NoSQL systems (e.g., Cassandra, MongoDB) do not natively support foreign keys. This is due to the challenges of maintaining referential integrity across distributed nodes. Instead, foreign key-like relationships are often handled at the application layer.

Examples of Foreign Keys

Let’s consider two tables: students and classes.

Students Table:

student_id	student_name	class_id
1	John Doe	1
2	Jane Smith	1
3	Jake Johnson	2

Classes Table:

class_id	class_name
1	Biology 101
2	Chemistry 101

In this case, the class_id column in the students table is a foreign key that references the class_id in the classes table. This means that each student belongs to a class, and the class information is stored in the separate classes table.

SQL Example with Foreign Keys

Creating the Classes Table (Parent Table):

CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL
);

Creating the Students Table (Child Table) with a Foreign Key:

CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

Inserting Data into Classes Table:

INSERT INTO classes (class_name) VALUES ('Biology 101'), ('Chemistry 101');

Inserting Data into Students Table:

INSERT INTO students (student_name, class_id) VALUES ('John Doe', 1), ('Jane Smith', 1), ('Jake Johnson', 2);

Best Practices for Using Foreign Keys

Foreign keys are essential for maintaining data integrity and ensuring relationships between tables in relational databases. However, improper use can lead to performance issues or complex dependencies. Following best practices ensures that your database remains efficient, scalable, and easy to manage. Here are some key guidelines to consider when using foreign keys:

1. Define Foreign Keys to Enforce Referential Integrity

The primary purpose of foreign keys is to enforce referential integrity between tables. Make sure that foreign keys are defined whenever there’s a relationship between two tables. Without foreign keys, it’s easy to end up with orphaned rows in the child table that don’t correspond to valid entries in the parent table.

Example: In a orders and customers table, define a foreign key on orders.customer_id referencing customers.customer_id to ensure that every order belongs to a valid customer.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

2. Create Indexes on Foreign Keys

Foreign keys are not automatically indexed, which can lead to slow performance in queries, particularly when JOINs or lookups are performed on large tables. Creating an index on the foreign key column in the child table improves query performance, especially when joining parent and child tables.

Best Practice: Create an index on the foreign key column to optimize JOIN performance.

 CREATE INDEX idx_orders_customer_id ON orders(customer_id);

3. Choose the Right ON DELETE/ON UPDATE Action

Carefully select cascading actions (ON DELETE CASCADE, ON DELETE SET NULL, etc.) depending on the relationship between the parent and child tables. While cascading actions can simplify data management, they can also introduce unintended consequences if not applied properly.

Use ON DELETE CASCADE when you want to automatically delete all child records when a parent record is deleted (e.g., deleting all orders when a customer is removed).
Use ON DELETE SET NULL when you want to preserve child records but remove the reference to the deleted parent.
Use ON DELETE RESTRICT to prevent deletion of a parent record if child records exist, ensuring explicit action.

 ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

4. Avoid Circular References

Ensure that foreign keys do not create circular dependencies between tables. Circular foreign key references can lead to difficulties when trying to insert or delete data due to dependency chains. In most cases, circular references are a sign of poor database design and should be avoided.

Example: Don’t allow Table A to reference Table B, while Table B references Table A. This can create deadlock situations during transactions.

5. Use Foreign Keys in Moderation for Performance

While foreign keys are essential for maintaining data integrity, they can introduce overhead during insert, update, and delete operations. Every time a foreign key is modified or a related record is deleted, the system must check the parent or child tables to ensure integrity.

Best Practice: For high-transaction tables, consider the performance impact of foreign keys and avoid using them unnecessarily. In such cases, you might manage referential integrity at the application layer.

6. Handle NULLs Appropriately in Foreign Keys

Foreign keys can allow NULL values, meaning that certain records may not have a relationship with the parent table. This can be useful when the relationship is optional, but it can also lead to data inconsistency if used improperly.

Best Practice: Use NULL values sparingly and only in cases where it makes sense for a foreign key to have no relationship. For example, if a student has not yet been assigned to a class, you could leave the class_id column in the students table as NULL.

7. Implement Cascading Actions with Care

Cascading actions such as ON DELETE CASCADE or ON UPDATE CASCADE can be powerful tools, but they should be used carefully, particularly in production environments. Cascading updates or deletions can affect multiple rows across different tables, potentially causing unintended data loss or propagation of incorrect updates.

Best Practice: Test cascading actions thoroughly in development environments and be cautious when applying them in production, especially in complex schemas with many relationships.

8. Consider Using Foreign Keys in Distributed Databases

In distributed databases (e.g., sharded environments or NoSQL databases), foreign key constraints are often not supported or difficult to implement due to the distributed nature of the data. In such cases, referential integrity is typically managed at the application layer.

Best Practice: If you’re using a distributed database like MongoDB or Cassandra, implement foreign key-like relationships in your application logic, ensuring that parent-child relationships are handled programmatically.

9. Use Deferred Constraint Checking (if supported)

In certain databases (e.g., PostgreSQL), foreign key constraint checking can be deferred until the end of a transaction. This is useful when you need to perform multiple operations that might temporarily break referential integrity but are resolved by the time the transaction completes.

Best Practice: Use deferred constraint checking for complex transactions where referential integrity might temporarily break, but is restored before the transaction commits.

 SET CONSTRAINTS ALL DEFERRED;

10. Document Foreign Key Relationships

When foreign keys are used extensively, it’s important to document the relationships between tables. Proper documentation helps developers and database administrators understand how tables are related and what cascading actions are in place, preventing accidental data corruption or unexpected behavior.

Best Practice: Use schema diagrams, comments in your SQL code, or an external document to clearly outline foreign key relationships and constraints.

 -- This foreign key enforces that every order belongs to an existing customer
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

11. Plan for Foreign Key Evolution

As your database schema evolves, foreign key relationships may need to be modified or extended. Plan for how you will handle changes to foreign key constraints, especially if you're working in an active production environment.

Best Practice: When modifying or dropping foreign key constraints, make sure to assess the potential impact on your application and data. Consider using database migrations to handle schema changes safely.

 -- Dropping a foreign key constraint
ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;

12. Test Foreign Key Constraints in Development

Before applying foreign key constraints in production, ensure they work correctly by testing them in a development or staging environment. Simulate various scenarios like deleting parent records, updating keys, and inserting invalid data to see how the foreign key behaves.

Best Practice: Create a test plan that covers typical and edge cases for your foreign key relationships. This ensures that your foreign keys are functioning as expected without causing unintended issues.

Foreign Key FAQs

What happens if I try to delete a parent record with existing child records?
- Without cascading actions, the database will throw an error and prevent the deletion because it would break the foreign key constraint. You can define a cascade delete to automatically delete the child records when the parent is deleted.
Can a table have more than one foreign key?
- Yes, a table can have multiple foreign keys, each referencing different tables or even different columns in the same table.
Can foreign keys contain null values?
- Yes, foreign keys can contain null values, which simply means that a row in the child table may not be related to any row in the parent table.
What are cascading actions?
- Cascading actions are automatic updates or deletions in child records when the corresponding parent record is updated or deleted. You can set ON DELETE CASCADE or ON UPDATE CASCADE to enforce this behavior.
Can a foreign key reference a non-primary key column?
- Yes, a foreign key can reference a column that has a unique constraint, not necessarily a primary key, as long as it ensures uniqueness in the parent table.
What is a parent and child table in terms of foreign keys?
- The parent table is the table that holds the primary key, and the child table is the one that contains the foreign key, establishing the relationship between the two.
Can I remove a foreign key constraint?
- Yes, you can remove a foreign key constraint using the ALTER TABLE statement.

ALTER TABLE students DROP FOREIGN KEY fk_students_class;

What is the impact of foreign keys on performance?
- Foreign keys ensure data integrity but can introduce some overhead, especially on insert, update, and delete operations. The performance impact, however, is generally small and worth the data consistency they provide.

Conclusion

Foreign keys are essential in relational databases for maintaining data integrity and enforcing relationships between tables. They link records between tables, ensuring consistency and preventing invalid data. By creating logical connections, foreign keys support efficient data management, normalization, and cascading actions.

Understanding the technical aspects—such as constraints, indexing, cascading actions, and handling NULL values—helps maintain performance and integrity. Following best practices, including indexing foreign keys and testing constraints, ensures efficient, scalable, and reliable database management.

Recommended Resources

Trino vs. StarRocks: Get Data Warehouse Performance on the Data Lake

Once praised for its data lake performance, Trino now struggles. Discover what's new in data lakehouse querying and why it's time to move to StarRocks.

5 Brilliant Lakehouse Architectures from Tencent, WeChat, and More

Explore 5 data lakehouse architectures from industry leaders that showcase how enhancing your query performance can lead to more than just compute savings.

Airbnb Builds a New Generation of Fast Analytics Experience with StarRocks

Learn from Airbnb's journey. Get a deep dive into how Airbnb developed their real-time data analytics infrastructure with StarRocks.