You Can Point a Foreign Key Where?!

Editor's Note: written entirely by Gemini (with minor edits by me)

Let’s talk about things we think we know, but it turns out we’ve (read: me) just been following muscle memory for twenty-plus years.

If you asked me on any given Tuesday what a foreign key does, I’d give you the standard textbook answer. It points to the primary key of a parent table. It’s bread-and-butter relational modeling. We back it with a sequence or an identity column, we join on the IDs, and we move on with our lives.

But a funny thing happened on the way to the database the other day. I realized, or rather, I was reminded, that the SQL standard and Oracle Database don’t actually care about your primary key.

A foreign key doesn't have to reference a PRIMARY KEY. It just needs to reference a minimal unique identifier. That means any column set with a valid UNIQUE constraint is fair game.

The Setup

Imagine you have a standard reference lookup table for order statuses. You’ve got your surrogate auto-incrementing ID as the PK because that’s what we do. But you also have an alphanumeric business code that the application actually uses, and that code is guaranteed unique.

CREATE TABLE order_statuses (
    status_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    status_code VARCHAR2(10) NOT NULL,
    description VARCHAR2(100) NOT NULL,
    --
    CONSTRAINT pk_order_statuses PRIMARY KEY (status_id),
    CONSTRAINT uq_order_statuses_code UNIQUE (status_code)
);

Normally, devs will map the status_id down to the child orders table. But what if you map the code instead?

CREATE TABLE orders (
    order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
    order_status VARCHAR2(10) NOT NULL,
    -- Look Ma, no status_id!
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_status 
        FOREIGN KEY (order_status) 
        REFERENCES order_statuses (status_code)
);

This compiles. It validates. It works.

Why Do We Care?

If you are a "data-first" person, this opens up some interesting pragmatic design choices, especially for seed data and reference enums.

1. No-Join Readability: When I run a quick SELECT * FROM orders, I don't see status 1, 2, or 3. I see 'PENDING', 'SHIPPED', or 'CANCELLED'. I don’t have to write an explicit JOIN to a lookup table just to debug a row in a terminal log.

2. CI/CD Sanity: Moving seed data across Dev, QA, and Prod environments when you rely purely on surrogate sequences can be a nightmare of dynamic mapping scripts. Business codes are immutable constants across environments. Your deployment scripts can just hardcode the literals without breaking things.

The Fine Print (Because this is Oracle)

Before you go rewriting your entire data model, remember that the laws of physics still apply.

First, Oracle does not automatically index foreign keys. If you point a child table to a parent’s unique business code, and somebody tries to delete or modify that code in the parent table, Oracle has to scan the child table to ensure no orphan records are left behind. If you didn’t manually put an index on orders.order_status, you are looking at a Full Table Scan and a nasty shared sub-exclusive table lock (TM) that will freeze concurrent operations.

Second, don't try this on your Slowly Changing Dimension (SCD) Type 2 tables. The second a business code repeats because you are tracking historical versions with effective dates, table-wide uniqueness breaks. And no, you can't use a partial function-based index to bypass this; declarative foreign keys need real, concrete constraints.

Relational Reality Check

In relational theory, a foreign key references a candidate key, which is simply a minimal superkey. The choice to elevate one candidate key to be the "Primary Key" is a physical implementation choice, not a logical requirement.

It’s completely valid, ANSI-standard behavior. It’s supported in Postgres and SQL Server too, so it’s not just an Oracle quirk.

It's just one of those elegant database features hiding in plain sight while application layers spend thousands of lines of code trying to reinvent referential integrity.

Keep it in the database.

Appendix: Documentation & Structural Foundations

Oracle Database Documentation

• Oracle SQL Language Reference — The constraint Clause: The definitive syntax rules and structural restrictions governing referential integrity, confirming that foreign keys can target primary keys or unique constraints.

  Oracle Database SQL Language Reference — Constraints

Edgar F. Codd & Relational Theory

• The 1970 Foundation Paper: Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks." Communications of the ACM. The original blueprint that introduced relational algebra, establishing that relationships are derived strictly by matching domains over mathematical relations rather than rigidly named primary/foreign key pairs.

  ACM Digital Library — A Relational Model of Data for Large Shared Data Banks

• The Relational Model for Database Management: Version 2 (Book): Codd, E. F. (1990). Addison-Wesley. Codd formalizes RM/V2, explicitly grouping Primary Keys and Alternate Keys together under the definition of Candidate Keys, proving that referential integrity mathematically depends on the candidate key property of uniqueness.

  ACM Digital Library — The Relational Model for Database Management (Version 2)