---
title: "foreign key"
description: "A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases."
canonical: "https://motherduck.com/glossary/foreign-key/"
related:
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# foreign key

> A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.

## Key Takeaways

- **Definition:** A Foreign Key (FK) is a column that links two tables together by referencing the Primary Key of another table.  
- **Purpose:** It maintains **Referential Integrity**, ensuring relationships between data remain consistent.  
- **DuckDB Context:** DuckDB supports standard SQL foreign key constraints, which are useful for ensuring data quality in your warehouse, though they incur a small performance cost during write operations.  
- **Usage:** Essential for joining dimensions and facts in Star Schema data modeling.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Foreign Key Definition
A **foreign key** is a column (or collection of columns) in a table that links to the **primary key** in another table. It is a critical component of relational database systems, used to establish and enforce a link between data in two tables. This link ensures **referential integrity**, meaning the database prevents you from having data in one table that points to non-existent data in another.

## Foreign Key Syntax in DuckDB

In DuckDB and other databases, you define a foreign key as part of your table creation using the `FOREIGN KEY` constraint. This tells the database to enforce the relationship between tables by preventing invalid references. Here's an example:

```sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  total DECIMAL(10,2)
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER,
  product_name VARCHAR(100),
  quantity INTEGER,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
```

In this example, `order_id` in the `order_items` table is a foreign key that references the `id` column in the `orders` table. DuckDB will prevent you from:
- Inserting an order item with an `order_id` that doesn't exist in the `orders` table
- Deleting an order from `orders` if there are still items referencing it in `order_items`

## Primary Key vs. Foreign Key

Understanding the distinction between these two keys is fundamental to relational database design:

| Feature | Primary Key | Foreign Key |
| :---- | :---- | :---- |
| **Uniqueness** | Must be unique for every row. | Can accept duplicate values. |
| **Nullability** | Cannot be NULL. | Can be NULL (unless specified otherwise). |
| **Function** | Identifies the record itself. | Identifies a relationship to another table. |
| **Count** | Only one per table. | A table can have multiple foreign keys. |

## Composite Foreign Keys
You can also create foreign keys that reference multiple columns, known as composite foreign keys:

```sql
CREATE TABLE locations (
  country_code CHAR(2),
  region_code CHAR(2),
  city VARCHAR(100),
  PRIMARY KEY (country_code, region_code)
);

CREATE TABLE stores (
  id INTEGER PRIMARY KEY,
  country_code CHAR(2),
  region_code CHAR(2),
  address VARCHAR(200),
  FOREIGN KEY (country_code, region_code) 
    REFERENCES locations(country_code, region_code)
);
```
## Foreign Keys in Data Warehousing

In transactional databases (OLTP), foreign keys are strictly enforced to prevent errors. in Data Warehousing (OLAP) scenarios like those used with MotherDuck, foreign keys play a dual role:

1. **Data Quality:** They prevent 'orphaned' records in your fact tables.  
2. **Join Optimization:** Defining foreign keys helps the query optimizer understand the relationships between tables, potentially speeding up JOIN operations in complex analytical queries.

## Performance Considerations
Foreign keys automatically create an index on the referencing columns in DuckDB, which can help with query performance when joining tables. However, they also add overhead during data modifications since the database must check referential integrity with each insert or update.

While DuckDB indexes foreign keys for faster joins, enabling constraints does check every INSERT or UPDATE. For massive bulk loads in a data warehouse context, engineers sometimes temporarily disable constraints to maximize load speed, re-enabling them afterwards to check integrity.