creating InnoDB table with primary key with multiple columns and auto increment

[arnott r]

Hi all,
why does the following create statement fail ?

CREATE TABLE test (
id INT NOT NULL,
id1 INT NOT NULL auto_increment,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id,id1),
INDEX name (last_name,first_name))
type=InnoDB
;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

we want to create InnoDB table because we want to be able to use 'delete on cacasde'.

is there a solution to this ?

Thanks
Arnott