Foreign Keys with Composite Key

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?

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.

StackExchange Database Administrators Q#39021, answer score: 11