Can a foreign key refer within the same table?

Yes, a self-referential relationship is possible.

Does a foreign key always prohibit null?

No. Nullability and matching rules must be specified.

Does an existing category prove correct classification?

No; it only satisfies that part of the structural rule.

AI Glossary / Data governance and quality / Advanced

Referential integrity

Definition

Referential integrity is the condition in which references between database records satisfy their declared rules. In relational databases, a foreign key commonly requires a reference to match a key in a referenced table, subject to the specified treatment of null values. It prevents a stored relationship from pointing to a missing target when that relationship is required to resolve. It does not establish that the relationship is factually correct.

What it means

Suppose a glossary article refers to a category by category ID. If that ID must match an existing category record, an article cannot validly refer to a nonexistent category. A reference to the wrong existing category, however, may still satisfy the rule.
IBM describes referential integrity in terms of valid foreign-key values. The referenced key need not always be the table's designated primary key; an eligible unique key can also serve, depending on the database's rules.

How it works

The designer declares the referencing columns, their target and relevant actions when data changes. The database checks changes against the enforced constraint. A single-column nullable foreign key may permit an absent reference, whereas a separate not-null requirement makes a value mandatory. Composite keys need explicit attention to their null-matching rules.
In PostgreSQL, delete actions include restricting a referenced deletion, cascading it to dependent rows or setting references to null where permitted. These choices represent different business decisions. An enforced relationship is not merely a line in a diagram: it affects which changes the database accepts. Applications must handle rejected operations and avoid interpreting them as successful saves.

Three practical examples

Category assignment: A publishing site stores categories separately from articles. The reference rule rejects an unknown category ID instead of silently accepting a dangling assignment. An editor still needs to judge whether an existing category is the appropriate one for the article's subject.
Review history: A fictional review record refers to an article ID. The team decides that deleting an article should be blocked while reviews depend on it. This preserves the relationship under that design; it does not by itself decide whether the business should archive an article instead of deleting it.
Optional mentor: A contributor record may refer to another contributor as a mentor. If the relationship is optional, a permitted null can represent no recorded mentor. A self-referential foreign key can require any supplied mentor ID to exist, but a separate rule may be needed to prohibit someone from being their own mentor.

Think of it this way

Think of a library catalog in which each loan slip must name a book that exists in the catalog. The check stops a slip from pointing to a nonexistent book number. It cannot tell whether the librarian selected the right existing book for the borrower. Referential integrity is the consistency check on the reference, while accurate recording remains a separate responsibility.

Worked example

In an invented staging dataset, Categories contains C1 and C2. Articles contains A1 referencing C1, A2 referencing C2, A3 referencing C9 and A4 with no category value. Assume the intended design uses a single-column foreign key and allows null because uncategorized drafts are permitted.
A1 and A2 have matching targets. A3 fails because C9 does not exist. A4 is permitted under the stated nullable design. Thus three proposed rows satisfy this particular relationship rule and one does not. If the business instead requires every article to have a category, adding the separate mandatory-value requirement would also reject A4. The staging dataset is being checked before admission; it is not presented as a valid committed state under an enforced constraint.
Now begin with only the valid A1 and A2 rows and consider deleting C1. Under the example's restrictive deletion policy, the operation is rejected while A1 references C1. Under a different cascading policy, deleting C1 would also delete A1. These outcomes are alternatives, not sequential steps. Both can preserve the declared relationship, but only the chosen policy can reflect the intended treatment of articles.

Illustrative case study

A fictional AI glossary team imports article metadata from several departmental lists. A category migration renamed labels and replaced some identifiers, leaving a mixture of old and new references in the incoming files.
Rather than forcing the import through, the team checks the proposed references against the agreed category register. It separates unknown IDs from optional blanks and from valid IDs whose meaning appears inappropriate. The first group needs mapping or correction, the second needs an explicit policy decision, and the third needs editorial judgment.
After the import, the team retains enforced reference rules and documents what should happen when a category is retired. This invented case separates structural and semantic review. Unknown identifiers should not automatically become new categories.

Common misconceptions and mistakes

Referential integrity is not a guarantee of complete data quality. A wrong but existing target can pass. Nullability and referential validity are also different concerns: an optional relationship may legitimately have no target. Cascading deletion is not automatically the correct cleanup strategy, because dependent records may have independent business value. Another mistake is assuming every database implements constraint timing and null behavior identically. Finally, a relationship drawn in a design document is not evidence that the deployed database actually enforces it.

Business use

For an AI publishing project, identify the relationships whose targets must exist: article-to-category, review-to-article or assignment-to-contributor, for example. Define optionality and lifecycle behavior before selecting constraints. When an import fails, preserve enough context to correct the reference instead of silently discarding the record or inventing a target. Review the semantic assignments separately. A useful quality report distinguishes missing targets, permitted absence and incorrect but structurally valid relationships so that each reaches the right owner.

Check your understanding

Why does A3 fail?

Answer: C9 is absent from Categories.

Why can A4 pass?

Answer: The example permits a null reference.

What additional rule would reject it?

Answer: A mandatory non-null value.

What happens to A1 under cascading deletion of C1?

Answer: It is deleted under that alternative policy.

Related terms

Foreign key
Primary key
Data Quality
Schema
unique constraint
null
cascading delete

Learn next

Sketch an article-category relationship. Specify whether it is optional and what should happen when a category is removed. Test a missing target, a blank reference and a wrong existing target separately.

Sources

PostgreSQL 18, Constraints: https://www.postgresql.org/docs/18/ddl-constraints.html IBM, historical Db2 SQL Reference, Constraints: https://public.dhe.ibm.com/ps/products/db2/info/vr6/htm/db2s0/cnstrnt.htm Historical IBM material supports the terminology; PostgreSQL documentation supports its stated behaviors. The category dataset and import case are original examples, not database migration instructions.

Previous: Referential density Next: Referential transparency