What Is a Foreign Key in a Database?
A foreign key is a column or combination of columns in a relational database table that refers to a primary key or another eligible unique key in another table. It establishes a relationship between records and helps maintain referential integrity across the database.
For example, an ORDERS table can contain a CustomerID column referencing the corresponding record in a CUSTOMERS table. This relationship ensures that an order cannot refer to a customer who does not exist, provided the foreign key constraint is enforced.
Foreign keys are fundamental to relational database architecture. They are widely used in SQL databases such as MySQL, PostgreSQL, SQL Server, and Oracle to organize related information and prevent invalid references between tables.
Foreign Key Example in SQL
Consider a database containing two related tables: CUSTOMERS and ORDERS. Each customer has a unique identifier, while every order stores the identifier of the customer who placed it.
CUSTOMERS Table
| CustomerID (PK) | CustomerName |
|---|---|
| 101 | John Smith |
| 102 | Mary Brown |
| 103 | David Wilson |
ORDERS Table
| OrderID (PK) | CustomerID (FK) |
|---|---|
| 5001 | 101 |
| 5002 | 102 |
| 5003 | 101 |
Here, CustomerID is the primary key in CUSTOMERS and a foreign key in ORDERS. Customer 101 can have multiple orders, but each order references one customer. This is a typical one-to-many relationship.
SQL Foreign Key Constraint Example
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
);The FOREIGN KEY constraint instructs the database to check references between the tables. An order with CustomerID 999 cannot be inserted if no matching customer exists, unless the constraint is disabled or not enforced. In this example, NULL is permitted because CustomerID was not declared NOT NULL.
Primary Key vs. Foreign Key
Both types of keys establish relationships within a relational database, but they serve different purposes. A primary key uniquely identifies each row in its own table, while a foreign key references records identified by a key in another table or, in some cases, the same table.
| Property | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identifies a row. | Establishes a reference to another key. |
| Unique Values | Must be unique. | May contain duplicate values. |
| NULL Values | Not permitted. | May be permitted unless restricted by other constraints. |
| Number per Table | One primary key constraint, possibly containing multiple columns. | Multiple foreign key constraints are possible. |
| Main Function | Row identification. | Referential integrity between records. |
Foreign Key Constraints and Referential Integrity
Referential integrity means that relationships between database records remain valid. A foreign key constraint prevents operations that would create invalid references, according to the rules configured for that relationship.
For example, deleting a customer who has existing orders may be prohibited, may automatically remove the customer's orders, or may set the referencing values to NULL. The result depends on the foreign key constraint definition.
INSERT and UPDATE Rules
When an application inserts or updates a foreign key value, the database checks whether the referenced key exists. Invalid values are rejected when they violate an enforced foreign key constraint.
DELETE and UPDATE Actions
When referenced records are deleted or their keys are modified, configured referential actions determine whether the operation is rejected or related records are updated automatically.
ON DELETE CASCADE, RESTRICT and SET NULL
SQL databases provide referential actions that define what happens to related records when a referenced row is deleted. Common options include the following:
ON DELETE CASCADE
Automatically deletes referencing rows when the referenced parent row is deleted. This can be useful for dependent records that should not exist independently.
ON DELETE RESTRICT
Prevents deletion of a referenced parent row while matching child records exist. It helps avoid accidental removal of records required by related tables.
ON DELETE SET NULL
Sets the referencing foreign key columns to NULL when the parent row is deleted. The columns must permit NULL values for this action to work.
SQL Example with ON DELETE CASCADE
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);In this example, deleting a customer automatically deletes that customer's orders. The CASCADE action must be explicitly configured where the database does not use it by default.
Note: ON DELETE NO ACTION is another common option. Its exact behavior and the timing of constraint checks depend on the database system. For example, PostgreSQL allows certain constraint checks to be deferred, while MySQL InnoDB treats NO ACTION and RESTRICT equivalently.
Foreign Keys in MySQL: InnoDB vs. MyISAM
In MySQL, foreign key enforcement depends on the table's database storage engine. The InnoDB engine provides foreign key constraints, transactions, and referential actions.
The older MyISAM engine does not enforce foreign key constraints. Although MyISAM remains relevant to some legacy databases, InnoDB is the default general-purpose storage engine for modern MySQL installations.
InnoDB
- Enforces foreign key relationships.
- Provides transactional operations.
- Allows referential actions such as CASCADE and RESTRICT.
- Uses row-level locking and transaction mechanisms.
MyISAM
- Does not enforce foreign key constraints.
- Does not provide transactions.
- Uses table-level locking.
- May appear in older MySQL applications and databases.
When planning a database migration or changing storage engines, verify how existing table relationships are implemented. A database application may rely on relationships enforced by application code rather than by the database engine.
Foreign Keys in Database Backup and Restore
Foreign key constraints become especially important when restoring a database from a logical backup. Tables can depend on one another, and importing records in the wrong order may produce referential integrity errors.
For example, restoring the ORDERS table before the referenced CUSTOMERS records are available may fail if the database enforces foreign key constraints during import.
Backup Considerations
- Include all related tables required by the application.
- Preserve table definitions, primary keys, foreign keys, and other constraints.
- Use an appropriate method to obtain a consistent database state.
- Keep the database schema and corresponding records together.
- Verify that the backup contains everything required for recovery.
Restore Considerations
- Restore referenced tables before dependent tables when required.
- Follow the restore order defined by the database backup format or utility.
- Handle foreign key checks according to the database system and restore procedure.
- Check for missing references and orphaned records after import.
- Test the restored application's queries and relationships.
Some SQL backup tools manage table dependencies or foreign key checks during restoration. For example, mysqldump produces logical MySQL backups containing SQL statements that can recreate database structures and records. The available options and generated statements affect how foreign key relationships are restored.
Disabling foreign key checks during import requires particular care. In MySQL, re-enabling foreign key checks does not automatically validate all existing rows that were inserted while checks were disabled. A completed import therefore does not necessarily guarantee referential integrity.
For additional information about recovery issues involving MySQL-compatible databases, see Troubleshooting MariaDB Backup Errors.
Backing Up Relational Databases with Handy Backup
Handy Backup provides automated backup and recovery tools for relational databases, including MySQL, MariaDB, PostgreSQL, MS SQL Server, and other database systems. Database backup tasks can be scheduled and configured to save copies to local, network, remote, or cloud storage.
The MySQL Backup plug-in can create database backups containing table structures and data. Foreign key relationships are preserved when the backup and restore procedure includes the relevant database definitions and dependent records.
Database administrators should distinguish regular backups from MySQL replication. Replication maintains copies of changing database data, while backups provide separate recovery points that can be used after accidental deletion, corruption, or other incidents.
Important: Backup software does not independently guarantee referential integrity. Successful recovery depends on database consistency, the backup method, the information included in the copy, and correct restoration procedures.
FAQ About Foreign Keys
What is a foreign key in SQL?
A foreign key is a column or combination of columns that references a key in another table or the same table. It defines a relationship between records and, when enforced, helps prevent invalid references.
Can a foreign key contain duplicate or NULL values?
Yes. Foreign key values do not have to be unique, so multiple rows can reference the same parent record. NULL values may also be allowed unless the column is declared NOT NULL or other constraints prevent them. Composite foreign key NULL behavior depends on the database system and constraint definition.
Can a table have multiple foreign keys?
Yes. A table can contain several foreign key constraints referencing different tables or multiple keys in the same table. A foreign key may also consist of more than one column.
Does deleting a parent record automatically delete child records?
Not necessarily. Automatic deletion occurs when the relevant foreign key is configured with ON DELETE CASCADE. Other actions may prevent deletion or set referencing values to NULL, depending on the database system and constraint definition.
Why do foreign keys cause database restore errors?
Foreign key errors may occur when dependent records are imported before referenced records, required parent rows are missing, or restored data violates existing constraints. Database restore procedures must account for relationships between tables and preserve referential integrity.
See also: