In this video, we will explore the concept of foreign keys and how they can be used to build and maintain data relationships within relational databases.
To start, it's important to understand the difference between foreign keys and foreign key constraints. A foreign key is a column or set of columns in a table that references the primary key of another table. This enables related data to be linked together in separate tables.
On the other hand, a foreign key constraint is a condition that ensures the referential integrity of the data by enforcing a relationship between the foreign key and the referenced primary key. This means that the constraint will guarantee that all data references are valid and consistent, preventing data from being added, updated, or deleted in a way that would break the relationships between tables.
It's worth noting that foreign keys can exist without constraints, but constraints are helpful to maintain referential integrity. Constraints also require additional computation to maintain, so at a certain scale, you may need to consider dropping some constraints if they become too costly in terms of performance.
Let's take a look at a simple example of creating two tables with a foreign key constraint. We'll start with a parent
table and a child
table. Here's the code to create the parent
table:
CREATE TABLE parent (
ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);
This creates a table parent
with a single column id
as a primary key. Now let's create the child
table with a foreign key constraint that references the parent
table:
CREATE TABLE child (
ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
parent_id BIGINT UNSIGNED,
FOREIGN KEY (parent_id) REFERENCES parent(ID)
);
This creates a table child
with two columns, id
and parent_id
. The parent_id
column references the primary key of the parent
table using a foreign key constraint. This constraint enforces the referential integrity of the data, ensuring that any data added to the child
table is consistent with the data in the parent
table.
However, it's important to note that when creating a foreign key constraint, the referenced column must be of the same data type as the referencing column. For instance, if the id
column in the parent
table is unsigned, the parent_id
column in the child
table must also be unsigned. Additionally, the length and character set of string columns used for referencing each other should match for optimal performance.
Now that we have our tables set up with a foreign key constraint, let's take a look at how data can be modified with this constraint in place.
First, let's insert some data into the child
table:
INSERT INTO child (parent_id) VALUES (1);
This code attempts to insert a record into the child
table with a parent_id
value of 1. However, since the parent
table is currently empty, this will fail because there is no data to reference.
Let's insert a record into the parent
table to correct that:
INSERT INTO parent (ID) VALUES (1);
Now that we have a record in the parent
table, we can successfully insert a record into the child
table:
INSERT INTO child (parent_id) VALUES (1);
The foreign key constraint ensures that this data is consistent and reflects a valid relationship between the two tables.
If we try to delete the record from the parent
table, we would encounter another issue:
DELETE FROM parent WHERE ID = 1;
This code attempts to delete the record from the parent
table with an ID of 1. Since there is still a record in the child
table that references this ID, the foreign key constraint will prevent this deletion from taking place.
To enable cascading deletes or nullifying deleted references, additional options can be set on the foreign key constraint. It is important to consider the scale and impact of these options to avoid unintended consequences such as excessive data deletion or corruption.
Foreign keys are an important tool for maintaining relationships and ensuring the integrity of data in a relational database. By linking tables together and enforcing referential integrity, foreign keys help ensure consistency and accuracy in data management.
While foreign key constraints can be expensive to maintain at a large scale, they are helpful to ensure data integrity in many situations.