Composite Foreign Keys

[NickZA]

Hi

Rather than posting in an old thread I thought I'd start this one, but basically what I need to do is outlined here:
http://www.thescripts.com/forum/thre...e+foreign.html

I am having trouble creating the surrogate key mentioned in that post; obviously it has to be autoincrementing, but I get the error "1075 - incorrect table definition; there can only be one auto column and it must be defined as a key".

This makes no sense really as the surrogate key column does not need to be a PK, that's the whole point -- it's acting as a surrogate for the combination of two other columns which actually are the PK (composite).

What do I need to do here?

-NickZA

[Atli]

Hi NickZA. Welcome to The Scripts!

The Auto_Increment column doesn't need to be a Primary Key, it only needs to be a Key. So, you can create a identity column that is Unique and Auto_Increment (given that no other column is Auto_Increment).

For example:

CREATE TABLE myTbl(
  ID INT Unsigned Unique Auto_Increment,
  PK1 INT Unsigned,
  PK2 INT Unsigned,
  PK3 INT Unsigned,
  Primary Key(PK1, PK2, PK3)
)