5

Imagine I have a table with the following dependency:

(Name, BoughtFrom, TimeBought) --> Cost

Where the three attributes in bold form a composite primary key for the table. Now then, I want to link (form a relationship) from another table to this one. How do I do that? If I had an ID column I'd know what to do, but I've never come across a scenario like this.

Would I have to add all three columns (Name, BoughtFrom, TimeBought) to the other table? Or is there another way?

  • 3
    Yes, you either add all 3 columns in the other table (and make the FK through those) or add a surrogate (ID) column in this one and make it unique (so you can make FKs to reference that). – ypercubeᵀᴹ Apr 1 '13 at 21:28
  • Thanks ypercube. Is a surrogate ID column any different from a regular ID column? Or are we calling it "surrogate" just cause it isn't necessary to have? – J86 Apr 1 '13 at 22:01
  • Yep, that's what these columns are called. English is not my native language so no idea if that comes from "not necessary" or "doesn't have a meaning" or something else: Surrogate key – ypercubeᵀᴹ Apr 1 '13 at 22:04
10

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like

foreign key (Name, BoughtFrom, TimeBought) 
  references the_other_table_name (Name, BoughtFrom, TimeBought)

If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}. You can do that with something along these lines.

create table your_table (
  your_id_number integer primary key,
  Name ... ,
  BoughtFrom ... ,
  TimeBought ... ,
  unique (Name, BoughtFrom, TimeBought)
);

The surrogate key doesn't have to be an ID number. It doesn't have to be any kind of number. But an automatically incrementing ID number is the most common.

Surrogate means takes the place of. A surrogate key, like an ID number, takes the place of a natural key. Kind of like a surrogate mother takes the place of a natural mother.

  • Thanks Mike. The bit about enforcing a unique constraint was what I couldn't wrap my head around. How does one apply a unique constraint to a three way combination? i.e. all three values together must be unique? not just one value of each attribute. I hope it's clear what I mean. – J86 Apr 1 '13 at 22:42
  • 1
    @Ciwan: Expanded my answer. – Mike Sherrill 'Cat Recall' Apr 2 '13 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.