Problem with mySQL 3.22

[ctimmer]

I have the following SQL that works on mySQL 4+:

CREATE TABLE nuke_multiimage
( nuke_multiimage.pn_group_name VARCHAR (40) NOT NULL DEFAULT '' ,
nuke_multiimage.pn_image_id INTEGER (10) NOT NULL AUTO_INCREMENT ,
nuke_multiimage.pn_enabled INTEGER NOT NULL DEFAULT 1 ,
nuke_multiimage.pn_first_date DATE NOT NULL DEFAULT '1000-01-01' ,
nuke_multiimage.pn_last_date DATE NOT NULL DEFAULT '9999-12-31' ,
nuke_multiimage.pn_image_data MEDIUMBLOB NOT NULL ,
nuke_multiimage.pn_image_type CHAR (10) NOT NULL DEFAULT '' ,
nuke_multiimage.pn_link TEXT NOT NULL DEFAULT '' ,
nuke_multiimage.pn_link_hist_flag INTEGER NOT NULL DEFAULT 0 ,
nuke_multiimage.pn_new_window_flag INTEGER NOT NULL DEFAULT 0 ,
nuke_multiimage.pn_text TEXT NOT NULL DEFAULT '' ,
PRIMARY KEY(pn_group_name, pn_image_id) )

But causes the following error on mySQL 3.22:
Incorrect table definition; There can only be one auto column and it must be defined as a key

Any ideas how to make this backward compatible?

Thanks for the help

[Pig]

I believe you could change it to use only one primary key:
PRIMARY KEY(pn_group_name, pn_image_id)
becomes
PRIMARY KEY(pn_group_name)

Not entirely sure.

[Sxooter]

Actually, I think the autoincrement has to be the pk in older MySQL versions so you might have to do something like:

create table ....

nuke_multiimage.pn_image_id INTEGER (10) NOT NULL AUTO_INCREMENT primary key,
...
unique (pn_group_name, pn_image_id) );