Primary and Foreign Keys

A primary key identifies a row using one or more columns. When enforced, it requires the complete key to be unique and its columns to be non-NULL. A table can exist without a primary key declaration; declaring one makes the identity rule explicit to the database.

For order lines, order_id alone repeats. The pair (order_id, line_number) can be a composite primary key: (O1, 1) and (O1, 2) are different lines. Two identical pairs violate the rule even if their other values differ. A text key does not automatically reject an empty string; that requires an additional rule.

A foreign key declares a reference to a primary or other eligible unique key, including a key in the same table. Many orders can refer to the same customer, so a foreign key is not itself a uniqueness rule. Declare NOT NULL separately when the reference must be present; composite references also require attention to the engine’s NULL rules.

Enforced keys can reject an orphan order but cannot establish that a customer is real or an amount is correct. Joins can be written without foreign-key declarations and with conditions other than equality. Verify the actual constraints and matching multiplicity before relying on a relationship.

Reference: DuckDB documentation. Work through the examples in SQL from Zero to Working.