Is a foreign key automatically unique in the referencing table?

No. Many orders can reference the same customer unless another constraint limits that.

Does a foreign key require a related value in every row?

Not necessarily. Nullability and matching rules determine whether an absent value is allowed.

Can a table reference itself?

Yes. A hierarchical relationship such as an employee's manager can use a self-referencing foreign key, with additional rules if cycles must be prevented.

AI Glossary / Data engineering and architecture / Intermediate

Foreign key

Definition

A foreign key is a database constraint that links values in one table to an eligible unique key in a referenced table. It helps ensure that a record does not refer to a nonexistent related record. A foreign key can involve one column or several columns together, and it can reference the same table. Its exact null-handling and referential actions depend on the declared constraint and database system.

What it means

If an order stores CustomerID, a foreign key can require that the identifier correspond to a customer record. This establishes referential integrity for that relationship. It does not prove that the order was assigned to the correct customer in the real world, that the customer is active, or that every customer has an order. The constraint enforces a specific structural rule, not every business meaning associated with the relationship.

How it works

Choose the referencing columns and the referenced unique key, then declare the relationship. The database checks relevant inserts and updates according to its constraint timing rules. Define what should happen when a referenced key changes or its row is deleted: possible actions include rejecting the operation, cascading it, or setting referencing values to null where allowed. Nullability is a separate design choice. Composite foreign keys must preserve the intended combination of values rather than validating each column independently against unrelated rows.

Three practical examples

Sales systems: Order.CustomerID references Customer.CustomerID so an order cannot use an unrecognized customer identifier.
Course platforms: Enrollment.CourseID references the course catalog, preserving the link between a participation record and its course.
AI data management: an annotation references the specific document version it describes, reducing the risk of storing annotations with missing or mismatched source versions.

Think of it this way

Think of a form that must use an entry from an official register. The reference is accepted only when the register contains the specified entry. The analogy is limited because database constraints can cover multiple fields, changes, and deletions automatically. Being listed in the register also does not prove that the chosen entry is the right one for the transaction.

Worked example

A toy Customer table contains C1 and C2. Order O1 references C1 and passes the foreign-key check. Order O2 references C9 and is rejected because C9 is absent. If CustomerID is optional and the declared rules allow NULL, an order with no customer value may be accepted; the foreign key alone does not make the relationship mandatory. Now attempt to delete C1 while O1 still references it. A restrictive rule can reject the deletion, while a deliberately configured cascading delete can remove the related order. Those outcomes are materially different, so the action must match the business requirement. If the business needs to retain historical orders, deleting them through a cascade would be an inappropriate design even though it preserves structural consistency.

Illustrative case study

Fictional example: Juniper Training imports enrollments before importing the course catalog. Foreign-key failures reveal unresolved course identifiers. The team loads the referenced courses first and places genuinely unknown identifiers in an exception workflow instead of inventing placeholder courses. It separately validates whether each enrollment refers to the intended offering. This invented case illustrates how a constraint can expose integration problems; it does not claim that referential integrity alone establishes the accuracy of every imported relationship.

Common misconceptions and mistakes

Do not assume foreign keys always reference a column named ID or only a primary key; eligible unique keys can also be referenced. Do not confuse a foreign key with an index, even though indexing may matter for performance. Avoid enabling cascading deletion simply to make an error disappear. Also remember that a valid foreign key to an inactive row is still structurally valid unless another rule says otherwise. Database-specific null and deferred-check behavior should be read rather than guessed.

Business use

Foreign keys make relationships explicit and prevent a useful class of orphan records. Review mandatory participation, deletion policy, update policy, and load ordering during design. Test migrations and bulk imports against realistic exceptions. For multi-tenant systems, ensure the key includes the correct scope where required so a valid identifier from another tenant cannot satisfy the wrong relationship. For AI datasets, use stable source and version relationships to support traceability, while retaining separate checks for label quality and semantic correctness.

Check your understanding

A ticket's CategoryID references an existing category row whose Active flag is false. Does the foreign key necessarily reject it?

Answer: No. The referenced key exists. Preventing new tickets from using inactive categories requires an additional rule that explicitly considers that state.

Related terms

Primary key
Composite key
Referential integrity
Integrity Constraint
unique constraint
orphan record
cascading delete
nullability
self-reference
schema relationship

Learn next

create two small related tables and test a valid reference, an unknown reference, a missing value, and a referenced-row deletion. Compare restrictive and cascading actions in a disposable test setup. Explain which behavior matches the retention and lifecycle requirements of the records.

Sources

Sources: PostgreSQL 18, Constraints, https://www.postgresql.org/docs/18/ddl-constraints.html ; PostgreSQL 18, CREATE TABLE, https://www.postgresql.org/docs/18/sql-createtable.html ; PostgreSQL 16, Foreign Keys tutorial, https://www.postgresql.org/docs/16/tutorial-fk.html . Primary documentation search excerpts consulted for foreign-key relationships and referential actions. Examples are original; product-specific eligibility, null matching, and timing details require the implementation's documentation.

Previous: Forecasting Next: Forensics