other dbs seem to support a table we don't

[Monty Taylor]

SQLAlchemy creates this for everyone else:

CREATE TABLE test2 (
 id INTEGER NOT NULL,
 id2 INTEGER NOT NULL AUTO_INCREMENT,
 data VARCHAR(50),
 PRIMARY KEY (id, id2),
 FOREIGN KEY(id) REFERENCES test (id)
);

BUT - for us we don't support it because of the auto_inc. Should we?

[Brian Aker (brianaker)]

We can remove the requirement that AUTO_INCREMENT generate an index, it is not required.

[Stewart Smith (stewart)]

On Thu, 03 Feb 2011 03:23:25 -0000, Brian Aker <email address hidden> wrote:
> We can remove the requirement that AUTO_INCREMENT generate an index, it
> is not required.

This would cause problems for InnoDB as the way it gets the max auto inc
number on startup is to do a reverse index scan reading the last record.

--
Stewart Smith

[Stewart Smith (stewart)]

The following should be added to docs:

InnoDB (and HailDB) don't store a current auto_increment value. In fact, the lower levels know absolutely nothing about auto_increment. The auto_increment feature is entirely implemented in the interface to Drizzle from the lower level storage. On opening a table, the index on the auto_increment column is found and then InnoDB/HailDB reads the last record in that index and sets the value of the auto_increment field in that row to the last used auto_increment value.

To support auto_increment on non-index fields, we would either have to maintain a current auto_increment number in another (system) table - having to update it in a sep txn for each auto_increment operation and generating lots of unneeded undo log OR on opening the table do a full table scan to find the current auto_increment value.