[CDBI] Auto increment with two-column primary keys

Matt S Trout dbix-class at trout.me.uk
Wed Jun 7 19:58:38 BST 2006


Tim Kearney wrote:
> 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.

Have a look at the way DBIx::Class' PK::Auto component handles it and 
see if you can figure out how to port that to CDBI (this may or may not 
turn out to be simpler than just porting your app to DBIC instead. YMMV, 
no warranty express or implied, etc. :).



More information about the ClassDBI mailing list