[CDBI] Sequences with multiple primary keys

Andrew Gianni agianni at buffalo.edu
Fri Jun 16 15:59:40 BST 2006


I'm working with an Oracle database and I'm having some trouble. The main
table in the schema has just one primary key and a sequence that fills it
(via a trigger). The one-to-many tables off of that table have at least two
primary keys: the foreign key from the main table as well as a unique id for
that table, which also comes from a sequence specific to that table. The
problem is that, even though I have defined the sequence for that table, I
still get errors when I try to insert a new record into the table. My code
looks something like:

{
package MainTable;
__PACKAGE__->columns(
    Primary => qw(
        main_id ) );

__PACKAGE__->columns(
    Other => qw(
        other_column1
        other_column2 ) );

__PACKAGE__->sequence( 'sequence_for_main_id' );

package SubTable;

__PACKAGE__->columns(
    Primary => qw(
        main_id
        sub_id ) );

__PACKAGE__->columns(
    Other => qw(
        other_column1
        other_column2 ) );

__PACKAGE__->sequence( 'sequence_for_sub_id' );
}

my $main_row = MainTable->insert({});

my $sub_table_row = SubTable->insert(
    {
     main_id => $main_row->main_id(),
     other_column1 => 'foo',
     other_columnd2 => 'bar',
    } );

The insert call generates:

Can't create Eptf::DB::PtfAccounts object with null primary key columns

>From reading the docs, I'm assuming that the sequence method only works when
there's a single primary key. I have already figured out a work around, but
it's a bit kludgey -- passing in dummy data for the key, which will be
replaced with the sequence by the trigger when the record is inserted:

my $sub_table_row = SubTable->insert(
    {
     main_id => $main_row->main_id(),
     sub_id => '99999999',
     other_column1 => 'foo',
     other_columnd2 => 'bar',
    } );

But then it's hard for me to get the new sub_id back:

my $new_sub_id = $sub_table_row->sub_id();

because C::DBI thinks that its value is 99999999 even though the database
slugged the next value from the sequence.

Any suggestions appreciated.

Andrew 

----

Andrew Gianni
Administrative Computing Services
Computing and Information Technology
University at Buffalo
215 MFAC, Ellicott Complex
Buffalo, NY 14261-0026
716.645.3587x7124




More information about the ClassDBI mailing list