Professional Web Applications Themes

Innodb and auto_increment; BDB engine? - MySQL

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?   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...

  1. #1

    Default 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?
     
    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

    wyo Guest

  2. #2

    Default Re: Innodb and auto_increment; BDB engine?

    == Quote from wyo (ch)'s article 
    > 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[/ref]

    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
    lark Guest

  3. #3

    Default Re: Innodb and auto_increment; BDB engine?

    > ..., yet 

    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/
    Willem Guest

  4. #4

    Default Re: Innodb and auto_increment; BDB engine?

    On May 25, 9:01 am, Willem Bogaerts
    <maardanzonderditstuk.nl> wrote: 
    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

    wyo Guest

  5. #5

    Default Re: Innodb and auto_increment; BDB engine?


     
    > 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".[/ref]

    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/
    Willem Guest

Similar Threads

  1. Replies: 4
    Last Post: April 26th, 03:16 PM
  2. INSERT and AUTO_INCREMENT
    By Ben in forum MySQL
    Replies: 7
    Last Post: December 30th, 11:15 PM
  3. which storage engine should I use- MyISAM or InnoDB?
    By crescent_au@yahoo.com in forum MySQL
    Replies: 2
    Last Post: November 9th, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139