Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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? –  Ciwan 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

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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. –  Ciwan Apr 1 '13 at 22:42
    
@Ciwan: Expanded my answer. –  Mike Sherrill 'Cat Recall' Apr 2 '13 at 0:26
    
Lovely, thank you Mike :) –  Ciwan Apr 2 '13 at 1:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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