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/thread647242-composite+foreign.html

I am having trouble creating the surrogate key mentioned in that post; obviously it has to be autoincrementin g, 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:
[code=mysql]
CREATE TABLE myTbl(
ID INT Unsigned Unique Auto_Increment,
PK1 INT Unsigned,
PK2 INT Unsigned,
PK3 INT Unsigned,
Primary Key(PK1, PK2, PK3)
)
[/code]