Is it possible to add an auto-increment field to a table with a multi-column key? For example, I would like a table something like this:
group_id: key int
sort_order: key int
id: auto-increment int
...other non key fields go here...
The reason I want the key on the group_id and sort_order (as an example, not my actual application) is so that I can take advantage of the clustered index on INNODB tables. I use this a lot on MS SQL Server to speed up queries.
For example, if I want to list all the records of group_id = 3 and output them in their sort_order, the clustered index means that I can retrieve all the records in one place and in the proper order.
Yes, I've tried putting a key on the "id" field after group_id and sort_order but I keep getting this error.
MySQL Error Number 1075
Incorrect table definition; there can be only one auto column and it must be defined as a key
As I mentioned, I did try defining it as a key and it is the only auto column on the table. Is this just a limitation of INNODB tables?
Any help would be greatly appreciated. Thanks.