[CDBI] Auto increment with two-column primary keys

Tim Kearney tkearney at nextwavemedia.com
Wed Jun 7 19:34:32 BST 2006


I'm new to the list, so I apologize if this has been covered before.

I'm have a table with a two-column primary key, where one column is an 
auto-incremented value.

The table is set up like:

    CREATE TABLE `user_resume` (
      `column_a` smallint(5) auto_increment,
      `column_b` smallint(5),
      `column_c` chat(10),
      PRIMARY KEY  (`column_b`,`column_a`)
    );

Allowing for data like:

    COLUMN_A COLUMN_B COLUMN_C
    -------- -------- ----------
    1        1        apple
    2        1        banana
    3        1        pear
    1        2        orange
    2        2        strawberry

I've read in the Class::DBI documentation that I cannot insert rows into 
this table without knowing what both of the keys in my two-column 
primary key are in advance.  Obviously, this doesn't allow me to use the 
auto_increment column as I had hoped.

I was wondering if anyone has come up with a suitable workaround to this 
issue.  I'd really love to be able to just say:

    $obj->insert( { column_b => 2, column_c => 'melon' });

I know there is a trigger/hook to call a subroutine before the insert 
takes place, where theoretically I could try to determine what the next 
id should be manually.  But doing it that way really isn't 
transaction-safe, and I was wondering if there was a way I could set up 
the table in Class::DBI to get this type of functionality to work.

Any help would be most appreciated.

Tim



More information about the ClassDBI mailing list