Foreign Key to non-primary key

sql

foreign-key

database-design

data-integrity

byAnton Shumikhin·Oct 14, 2024

A foreign key can reference a unique key from another table, not just primary keys. Ensure the column you are referencing has a UNIQUE constraint to uphold data integrity. Here is a practical example:

-- Hulk Smash table, no Primary Key needed!
CREATE TABLE Hulk (
    SmashFactor INT UNIQUE,
    ...
);
-- Avengers Assemble table with foreign key reference to hulk smash factor
CREATE TABLE Avengers (
    HulkSmash INT,
    ...
    FOREIGN KEY (HulkSmash) REFERENCES Hulk(SmashFactor)
);

In this example, HulkSmash in Avengers points to SmashFactor in Hulk, not a primary key but uniquely identifiable.

When to consider Non-Primary Key/Unique Key relationship

Legacy systems

Deal with legacy databases with finesse. Sometimes these have natural keys that are not primay keys but are definitely unique. Elegant solution? Make a foreign key reference!

Swift querying

Indexed non-Primary Key columns sometimes provide faster reads due to better alignment with your wild SQL queries. Predominantly static data? This is your best bet!

Soft deletes

Soft-delete flags can be tricky. Consider linking to a unique field that acts as an active flag indicator rather than try wrestling with rows that are not physically deleted.

Potential pitfalls and best practices

Data integrity

Ensure uniqueness constraint is maintained to avoid chaos in your data. Don't let referencing column's uniqueness constraint be altered!

Code compatibility

Assess the impact on application logic before any modification. A change in schema could send ripples and you may end up rewriting vast sections of your application.

Check constraints

PAIR PROGRAMMING with CHECK constraint to ensure data consistency when dealing with validation scenarios that your single referential constraint might not cover.

Non-Primary Key Foreign Keys - taking it up a notch

Complex relationships

In denormalized schemas, foreign keys to non-primary keys may be essential to deal with complicated relationships in a more pragmatic way.

Unique Identifiers

Remember that in one-to-one relationships it's possible to have a foreign key also serve as a primary key. It's the SQL version of "killing two birds with one stone".