Common Mistakes with Primary and Foreign Keys

Primary and foreign keys are the actual backbone of relational database integrity — not a formality, but the mechanism that makes every other design decision in this module mean something. A primary key uniquely identifies each record in a table; a foreign key establishes and enforces a real link between related tables. Get either one wrong, and the consequences aren't abstract: redundancy, data anomalies, and referential integrity that silently erodes over time. Below are the mistakes that show up most often when defining these columns.

Every table in a relational database needs a primary key. Without one, there's no guaranteed way to uniquely identify a given row — which means duplicate or inconsistent data isn't just possible, it's only a matter of time. Define an explicit primary key on every table. Don't rely on "this combination of columns happens to be unique in practice today" as a substitute; that's an assumption, not a guarantee.

Primary keys should never be built from meaningful, real-world business data — phone numbers, email addresses, Social Security numbers, product SKUs. These values feel like natural, guaranteed-unique identifiers, but "guaranteed" is doing a lot of work in that sentence: people change email addresses, phone numbers get reassigned, SKU schemes get reorganized. When a primary key value changes, every foreign key referencing it has to change too — and that update has to cascade across every referencing table simultaneously, locking rows and tables while it happens. On a small table, that's a brief inconvenience. On a large, heavily-referenced table, it's a real operational event.

The fix is to default to a surrogate key: an arbitrary, immutable identifier — an auto-incrementing integer sequence, or a UUID — that carries no business meaning of its own and therefore never needs to change because the business changed. Surrogate keys keep referential updates clean and keep your schema stable even as the real-world data it describes evolves underneath it.

Foreign keys are what actually enforce the logical connections between tables over time — not just at the moment data is entered, but permanently, on every subsequent insert, update, and delete. Skip the foreign key constraint, and nothing is actually stopping orphaned records from accumulating: a child row can end up pointing at a parent that no longer exists, and the database will never complain, because you never told it to. Always identify and explicitly declare foreign key relationships when you create your schema — don't let them exist only as a naming convention or an assumption in application code.

Here's a detail that surprises a lot of developers: defining a foreign key relationship does not automatically create an index on the referencing column, in systems like Oracle Database or SQL Server. A primary key almost always gets its supporting index for free. A foreign key generally does not.

The cost of missing that index shows up exactly when you can least afford it: deleting a parent record forces the database to scan the entire child table to make sure no orphaned rows would result, and any heavy JOIN across the relationship does the same. On a small table, you'll never notice. On a table with millions of rows, an unindexed foreign key can turn a routine delete or join into a query that takes minutes instead of milliseconds. Always index your foreign key columns explicitly — don't assume the database did it for you just because it enforces the relationship.

A subtler mistake, and a genuinely costly one: defining a primary key as an INT but its corresponding foreign key as a BIGINT — or pairing VARCHAR columns with different lengths or collations across the two tables. It looks harmless. It isn't.

When you join tables whose key columns don't match in type, length, or collation, the query optimizer is forced to perform an implicit type conversion on one side of the join before it can compare values at all. That conversion doesn't just add a small amount of overhead — it typically prevents the optimizer from using the index on that column altogether, turning what should be an efficient indexed lookup into something far slower. The fix is simple to state and easy to overlook: keep primary and foreign key columns in exact parity — same type, same length, same collation — every time.

A foreign key in one table typically references a primary key in another — but that's the common case, not the only one. A foreign key can reference any unique key. The actual requirement is narrower and more precise than "references a primary key": the referenced column, or set of columns, simply has to be guaranteed unique, so the database can reliably maintain one-to-one or one-to-many consistency. This gives you real flexibility to reference a candidate key when that's genuinely the more appropriate relationship, rather than treating "must point at a primary key" as an unbreakable rule.

When you're resolving a many-to-many (M:N) relationship, you need a linking table — also called an associative entity — and that table needs to include the primary keys from both related entities, together forming a composite primary key. For example:

CREATE TABLE StudentCourses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

This guarantees that each student–course pairing is unique, and prevents the same enrollment from being recorded redundantly.

One related mistake worth calling out specifically: a child table's foreign key referencing only part of a composite primary key. If a parent table's key is genuinely composite — say, TenantID plus OrderID in a multi-tenant system — a foreign key that only constrains on OrderID lets records slip through that reference an order belonging to an entirely different tenant. That's not a minor inconsistency; it's referential integrity silently breaking across a boundary that should never be crossed. The fix is to reference the full composite key, every column of it — or, if that's proving awkward throughout the schema, to refactor the parent table onto a single surrogate key instead.

ON DELETE CASCADE is a genuinely convenient way to keep child records from becoming orphans automatically — delete the parent, and the database cleans up everything that depended on it, no extra code required. In a complex operational schema, that convenience is also a liability. Deleting one top-level entity can silently wipe out downstream data you actually needed to keep — audit logs, transaction history, permission records — permanently destroying information that has real historical value, with no warning and no way to get it back afterward.

For records where that history matters, prefer a soft delete instead — an IsActive flag (or similar) that marks a record as retired without physically removing it, preserving everything that referenced it. Where you genuinely want the database to prevent accidental loss, ON DELETE RESTRICT forces whoever's deleting the parent to handle dependent cleanup deliberately, in application code, rather than letting a single delete statement cascade silently through the schema.

Normalization exists to eliminate redundancy, and it's essential — but taken to an extreme, it works against you. A highly normalized schema scatters related data across an enormous number of small tables, and every one of those extra tables is another join your queries need, which adds up to real complexity and real performance cost. Consider your actual workload and access patterns: if most interactions with the data go through stored procedures or a tightly controlled application layer, you can often relax normalization slightly without introducing genuine data inconsistency risk.

It's entirely possible to normalize your way into an unusable schema. Split every single attribute into its own table, and you can technically land in Fifth Normal Form (5NF) while producing something no one can actually query without a dozen joins. A practical schema balances normalization against usability and real-world performance — theoretical purity isn't the goal; a schema that's both correct and workable is.

Effective key design comes down to finding that balance: strong enough to genuinely enforce data integrity, flexible enough to evolve as the business does. Aim for:

• Stable, surrogate primary keys
• Accurate, explicitly enforced foreign keys, properly indexed and type-matched
• Deliberate choices around cascading behavior, rather than defaulting to ON DELETE CASCADE everywhere
• Thoughtful normalization, guided by actual workload and clarity rather than theoretical completeness

The next lesson explores mistakes related to relationships and referential integrity in greater depth.

Before moving on to the next lesson, click the Quiz link below to reinforce your understanding of common database design mistakes.
Database Design Mistakes - Quiz