Start with two related tables
Imagine a small shop with a Customers table and an Orders table. Each customer can place several orders, but each order belongs to one customer. Rather than copying a customer’s name and address into every order row, the Orders table can store a customer_id value that points to a row in Customers. This is the basic idea behind connecting relational tables: keep distinct kinds of records in separate tables, then store a link between them.
The link needs two roles. A primary key identifies a row within its own table. A foreign key holds a value that refers to a row in another table, or sometimes to a row in the same table. In this example, Customers.customer_id can be the primary key, while Orders.customer_id can be a foreign key referencing it. Orders also needs its own identifier, such as order_id, because identifying an order is different from identifying its customer.
What a primary key guarantees
A primary key must be unique and cannot be null. If Customers has two rows, they cannot share the same primary-key value, even if the people happen to have the same name. This makes the key a reliable way to select, update or refer to one particular row. A table has one primary-key constraint, although that constraint can cover more than one column.
A single generated ID is often convenient: customer_id might contain a value such as 42 while names and email addresses remain ordinary attributes. An ID is not the only option, however. In an OrderItems table, the pair (order_id, line_number) might serve as a composite primary key: an order can have multiple lines, but no two lines within that order share the same number. The useful question is whether the chosen value, or combination of values, identifies each row consistently over time. A person’s name is usually a poor choice because names can be shared or changed.
What a foreign key guarantees
A foreign-key constraint tells the database which values are valid for a referencing column. If Orders.customer_id references Customers.customer_id, an order cannot use customer_id 42 unless that customer exists. The referenced column must have an appropriate uniqueness constraint, commonly a primary key or unique key. The foreign key does not have to be unique: many orders can refer to the same customer.
Whether Orders.customer_id may be null is a separate design decision. A nullable foreign key can represent an order with no assigned customer; a NOT NULL constraint rules that out. A foreign key also does not prove that every customer has an order. It protects references from an order to a customer, not the existence of orders for each customer.
When a referenced customer is deleted or its key changes, the database needs a rule for affected orders. Depending on the database and the constraint configuration, options can include rejecting the change, cascading it to referencing rows, or setting the foreign-key value to null where null is allowed. Cascading can be useful, but deleting a customer and automatically deleting their orders may be undesirable. Choose a rule that matches what those records mean to the application.
Reading relationships in everyday queries
To display each order with its customer’s name, a query joins Orders to Customers where Orders.customer_id equals Customers.customer_id. The foreign-key constraint keeps those stored values consistent; the join is the operation that brings columns from both tables into a result. A normal inner join returns orders with matching customers. A left join starting from Customers can also show customers who have placed no orders.
Key constraints and indexes solve related but different problems. A primary key is generally backed by an index or equivalent database structure to support uniqueness and lookup. A foreign-key column is not necessarily indexed automatically; behavior varies by database. If a team frequently looks up orders by customer_id, an index on Orders.customer_id may help, but indexes also take storage and add work to writes. Check the database system and query needs rather than assuming every key needs the same indexing choice.
A practical design checklist
For each table, decide what one row represents before choosing its primary key. Then ask which other rows it must refer to. An OrderItems row, for example, might refer to both an order and a product through separate foreign keys. Those links say which order contains the line and which product the line describes. If the same product can appear on multiple order lines, its product_id should not be unique in OrderItems.
Finally, check the edge cases: Can the relationship be absent? Can a parent row be deleted while related rows remain? Are key values likely to change? Answering these questions helps determine nullability and update or delete behavior. Primary and foreign keys are not merely labels for columns; they let the database enforce the basic connections that the rest of the application relies on.
