How should I define a composite foreign key for domain constraints in the presence of surrogate keys

Posted by Samuel Danielson on Stack Overflow See other posts from Stack Overflow or by Samuel Danielson
Published on 2010-03-31T14:20:46Z Indexed on 2010/03/31 14:23 UTC
Read the original article Hit count: 495

I am writing a new app with Rails so I have an id column on every table. What is the best practice for enforcing domain constraints using foreign keys? I'll outline my thoughts and frustration.

Here's what I would imagine as "The Rails Way". It's what I started with.

Companies:
    id: integer, serial
    company_code: char, unique, not null

Invoices:
    id: integer, serial
    company_id: integer, not null

Products:
    id: integer, serial
    sku: char, unique, not null
    company_id: integer, not null

LineItems:
    id: integer, serial
    invoice_id: integer, not null, references Invoices (id)
    product_id: integer, not null, references Products (id)

The problem with this is that a product from one company might appear on an invoice for a different company. I added a (company_id: integer, not null) to LineItems, sort of like I'd do if only using natural keys and serials, then added a composite foreign key.

LineItems (product_id, company_id) references Products (id, company_id)
LineItems (invoice_id, company_id) references Invoices (id, company_id)

This properly constrains LineItems to a single company but it seems over-engineered and wrong. company_id in LineItems is extraneous because the surrogate foreign keys are already unique in the foreign table. Postgres requires that I add a unique index for the referenced attributes so I am creating a unique index on (id, company_id) in Products and Invoices, even though id is simply unique.

The following table with natural keys and a serial invoice number would not have these issues.

LineItems:
    company_code: char, not null
    sku: char, not null
    invoice_id: integer, not null

I can ignore the surrogate keys in the LineItems table but this also seems wrong. Why make the database join on char when it has an integer already there to use? Also, doing it exactly like the above would require me to add company_code, a natural foreign key, to Products and Invoices.

The compromise...

LineItems:
    company_id: integer, not null
    sku: integer, not null
    invoice_id: integer, not null

does not require natural foreign keys in other tables but it is still joining on char when there is a integer available.

Is there a clean way to enforce domain constraints with foreign keys like God intended, but in the presence of surrogates, without turning the schema and indexes into a complicated mess?

© Stack Overflow or respective owner

Related posts about foreign-keys

Related posts about surrogate-key