# Primary and foreign keys

> A primary key names each row uniquely; a foreign key points to a row in another table.

- Level: Intermediate
- Topics: Data
- URL: https://tidbit.legios.com.ar/primary-and-foreign-keys/
- In Spanish: [Claves primarias y foráneas](https://tidbit.legios.com.ar/es/primary-and-foreign-keys/)

In a relational database, a **primary key** is the column (or set of columns) that identifies each row uniquely. In a `customers` table it is usually an `id`: customer 42 is always that customer and no one else, even if two people share the same name. The database refuses a second row with the same primary key and does not allow it to be empty.

A **foreign key** is a column in one table that holds the primary key of a row in another table. An `orders` table has a `customer_id` column, and each value in it points to a row in `customers`. That is how a database links data without copying it: the order does not repeat the customer's name and address, it just says "customer 42", and a SQL `JOIN` brings the two together when you need them.

Foreign keys also protect consistency. If they are declared in the database schema, the database will not accept an order for customer 999 when there is no customer 999, and it can stop you from deleting a customer who still has orders. Many analytics systems skip these checks for speed, which is one reason data cleaning so often finds orphan rows.

## An analogy

A national ID number is a primary key: one per person, never repeated. When a hospital file writes your ID number instead of copying your whole life story, that number is a foreign key pointing back to you.

## Keep in mind

- Primary key: unique and never empty, one per row.
- Foreign key: a column that refers to a primary key in another table.
- Lets tables connect without duplicating data; joins follow these links.
- Declared keys stop orphan records, like an order for a customer who does not exist.

## Go deeper

- [Primary key on Wikipedia](https://en.wikipedia.org/wiki/Primary_key)
- [Foreign key on Wikipedia](https://en.wikipedia.org/wiki/Foreign_key)
- [Constraints, including keys (PostgreSQL docs)](https://www.postgresql.org/docs/current/ddl-constraints.html)

## Related terms

- [Database](https://tidbit.legios.com.ar/database/): An organized store of data that programs can search and update safely.
- [SQL](https://tidbit.legios.com.ar/sql/): The language you use to ask a database questions.
- [Database schema](https://tidbit.legios.com.ar/database-schema/): The blueprint of a database: which tables exist, their columns and how they connect.
- [Data cleaning](https://tidbit.legios.com.ar/data-cleaning/): Fixing errors, gaps and duplicates in data before anyone trusts it.