LLM Best Practices

Foreign key

Last updated 2 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A foreign key (FK) in a child table must reference an existing row in a parent table’s primary or unique key. The database rejects inserts that would orphan a child and parent deletes that would strand children, even under concurrent writes and bulk imports, where application checks let orphans slip in. Declare one for every parent-child relationship and choose the ON DELETE action deliberately.

Rules

  • The default action is NO ACTION: the violation is raised at the end of the statement, and the check can be deferred to commit if the constraint is declared DEFERRABLE. RESTRICT blocks immediately and cannot be deferred. CASCADE deletes children; SET NULL and SET DEFAULT reset them.
  • PostgreSQL does not index the referencing column. Add an index, or parent deletes and key updates scan the whole child table. The same index serves per-row lookups in an n-plus-one-query pattern.
  • FK checks lock the referenced row and can join a deadlock cycle; keep transactions short.
  • Skip an FK on a write-hot table only with measured evidence, and enforce the relationship elsewhere.
CREATE TABLE orders (
  id      bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT
);
CREATE INDEX orders_user_id_idx ON orders (user_id);

Backlinks