What is a database Foreign Key? A beginner's tutorial

What is a Foreign Key in a Database?#

Foreign keys are structured into a database as a common component linking together two tables. A foreign key must always reference a primary key elsewhere.

The original table is called the parent table or referenced table, and the referencing table with a foreign key is called a child table.

Foreign key references are stored within a child table and links up to a primary key in a separate table.

The column acting as a foreign key must have a corresponding value in its linked table. This creates referential integrity.

We must be careful when we create tables or alter tables (such as inserting or deleting data from the foreign key column) to avoid altering or destroying the linkage between them.

The CUSTOMER_ID in the order table becomes the foreign key that references parent or primary keys in the customer table.

Note: To insert an entry into the order table, the foreign key constraint must be satisfied.

If we try to input into CUSTOMER_ID using data not present in the customer table, we’ve damaged the integrity of the table’s referential powers.

Constraint FK#

Bridging data accurately is a prime directive. Software integrations and the ability to securely share data between applications are all dependent on data integrity and database relationships.

This is where constraint comes in. Foreign keys are often constrained to ensure the user cannot take actions that would damage dependency links between tables. This also constrains users from entering invalid data.

We can use foreign key restraints to help maintain referential integrity of our foreign key relationships. There are many referential actions we can use for constraint, including:

• Cascade: when deleting primary key values, the matching column in the child table are deleted

• Set null: when a referenced row is deleted/altered, the referencing values in the foreign key are set to null

• Restrict: Values in the parent table cannot be deleted if they are referred by a foreign key.

• Set default: The foreign key values in the child table are set to a default value if the parent table is altered/deleted

When it comes to foreign key constraint naming, we need to follow these general rules:

• The CONSTRAINT symbol value is used, and it must be unique in the database.
• For InnoDB tables, a constraint name is generated automatically if the constraint symbol clause is not defined
• For NDB tables, the FOREIGN KEY index_name value is used, or a constraint name is generated automatically.

Foreign Key versus Primary Key#

Unlike a foreign key, a primary key works within a relational database to uniquely identify specific records. Primary keys must be unique to the table and are often used as an absolute reference point for other types of database keys.

Foreign keys are used more as a link than as a unique identifier of a specific row or record.

While multiple foreign keys can be utilized within a specific table or relational database, only one primary key is permitted per database. Duplicate values are permitted for foreign keys, as well as null values.

Null values are not permitted for primary keys, and references to any primary keys within a table must be deleted before removing a primary key row/record.

Foreign Key versus Composite Key#

Composite keys within a relational database are used to combine two or more columns within a specific table, creating a unique identifier for that combination of columns.

While technically a candidate key as the composite key verifies uniqueness, composite keys are only formed when the particular column or columns are used in combination with each other.

Like foreign keys, composite keys can be used to link together multiple tables within a relational database. Unlike foreign keys, composite keys can be defined as a primary key during the creation of some SQL tables.

Foreign Key Referential Actions#

Referential integrity is constrained by foreign keys, ensuring that values in a particular table match values that are found in a different table.

These referential actions reinforce the integrity of the table structure, reducing the possibility of error by ensuring that referenced columns only contain unique sets of values.

Foreign keys can also accept null values, but it’s important to note that this may restrict their ability to protect the integrity of the referenced column, as null values are not checked.

Tip: Best practices indicate using a NOT NULL constraint when creating foreign keys to maintain the structural integrity of the database.

Creating a data structure that is flexible and extensible enough for long-term use can become increasingly difficult as data complexity and volume soars. The addition of unstructured data can easily result in errors.

Foreign keys are an extremely valuable component, helping ensure that your database is clear, consistent, and able to rapidly deliver accurate results.

Foreign keys in SQL and MySQL#

Let’s take a look at some syntax using SQL and MySQL. The following example creates a FOREIGN KEY on the “PersonID” column.

Learn more about different database types here

MySQL

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

The following syntax allows us to name FOREIGN KEY constraint:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

SQL real-world example#

Now let’s get more specific. Below, the Actors table is the referenced table and called the parent table. Here, the referencing table DigitalAssets is the child table.

We declare a column as a foreign key in a child table only if the column has an index defined on it. If the column doesn’t have an index it can’t be used as a foreign key.

For our example, we alter our DigitalAssets and set the ActorID column to be the foreign key as follows:

ALTER TABLE DigitalAssets
ADD FOREIGN KEY (ActorId)
REFERENCES Actors(Id);

INSERT INTO DigitalAssets
VALUES ("www.dummy.url", "instagram", "2030-01-01 00:00:00", 100);

The manager’s ID will reference the employee ID in the same column, and the employee ID will act as a foreign key.

What to learn next#

Congrats! You should now have a good idea how foreign keys can be used in your databases to make them more useful and secure. There is still a lot to learn. Next, you should check out:

• Drop a foreign key constraints
• SQL foreign key on alter table
• Triggers
• Foreign keys with MariaDB
• and more

To get started with these concepts, check out Educative’s course Database Design Fundamentals for Software Engineers to learn about the fundamental concepts of databases. You’ll learn why and when they’re used, what relational databases are, and entity-relationship diagrams.

If you already have some experience with databases, Educative’s course An Introductory Guide to SQL is another great step. You’ll learn the basics of SQL such as how to create a database, how to insert, query, and update data.

Happy learning!

Continue reading about databases and SQL#

• System Design Interview
• What are SQL Joins? Types of SQL joins explained
• What are database schemas? 5 minute guide with examples