The name "creatorUser" fails when there is a multi-column foreign key relationship.
As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.
Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".
My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).
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.
Best Answer
Sure, there is a big difference. In first case you are referencing by combination of columns. It means ParentTable should contain a row for each combination of OrderId and CompanyId which you want to add to your ChildTable. In the second case it would be enough to have separate rows with corresponding OrderId and corresponding CompanyId.