Innodb and auto_increment; BDB engine?

Since I'm redesigning my DB I wanted to switch to Innodb tables, yet
Innodb doesn't allow for auto_increment on secondary primary key
columns. Is there a workaround this problem?

>From the docs the BDB engine should support auto_increment on
secondary primary key columns and transactions. Yet it seems BDB isn't
used very much. Does anybody have experience with the BDB engine?
Performance numbers?

O. Wyss

== Quote from wyo (otto.wyss@orpatec.ch)'s article
> Since I'm redesigning my DB I wanted to switch to Innodb tables, yet
> Innodb doesn't allow for auto_increment on secondary primary key
> columns. Is there a workaround this problem?
> >From the docs the BDB engine should support auto_increment on
> secondary primary key columns and transactions. Yet it seems BDB isn't
> used very much. Does anybody have experience with the BDB engine?
> Performance numbers?
> O. Wyss

If you'd like to have access to row level locking and transaction processing, then
Innodb is a good choice for you. Innodb provides some other nice features as well
but these two are the primary features of it.
--
POST BY: PHP News Reader

> ..., yet
> Innodb doesn't allow for auto_increment on secondary primary key
> columns.

Sorry, I don't understand what you are trying to say. Could you give an
example?

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On May 25, 9:01 am, Willem Bogaerts
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> Sorry, I don't understand what you are trying to say. Could you give an
> example?
>
CREATE TABLE Attribute (
ID INT NOT NULL,
SubID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID, SubID),
);

This isn't allowed with InnoDB while MyISAM and according to the docs
BDB does. "SubID" may not use "AUTO_INCREMENT".

O. Wyss

>> Sorry, I don't understand what you are trying to say. Could you give an
>> example?
>>
> CREATE TABLE Attribute (
> ID INT NOT NULL,
> SubID INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (ID, SubID),
> );
>
> This isn't allowed with InnoDB while MyISAM and according to the docs
> BDB does. "SubID" may not use "AUTO_INCREMENT".

I see. You can make SubID the primary key (if you want to) and define a
unique index for the compound key:

CREATE TABLE Attribute (
ID INT NOT NULL PRIMARY KEY,
SubID INT NOT NULL AUTO_INCREMENT,
UNIQUE INDEX (ID, SubID)
);


--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/