[Dbix-class] DBIC with NULL Primary Key and fails

[Justin Jereza]

Hello.
I've got table_a(id SERIAL PK, v TEXT), table_b(id SERIAL PK, v TEXT),
and table_c(id SERIAL PK, x REF table_a(id), y REF table_b(id), z REF
table_b(id)). When I try to insert data to table_a and table_b through
table_c, DBIC errors saying that it tried inserting a NULL value in
the PK column. Why does this happen?

Relevant test code.
<snip>
$schema->resultset('Asm::Names')->create( {
        first_name => { v => 'foo' },
        middle_name => { v => 'bar' },
        last_name => { v => 'baz' },
} );
</snip>

Code for everything is after the error messages. I'm running DBIC
version 0.08007 and using it to access a PostgreSQL 8.1 database on
Debian Etch.

TIA

The short error message:

DBIx::Class::ResultSet::create(): DBI Exception: DBD::Pg::st execute
failed: ERROR:  null value in column "id" violates not-null constraint
 [for Statement "INSERT INTO dict.families (id, v) VALUES (?, ?)" with
ParamValues: 1=undef, 2='bar'] at DB.pl line 13

The long error message:

SELECT me.id, me.v FROM dict.families me WHERE ( ( ( me.v = ? ) AND (
me.id IS NULL ) ) ): 'bar'
SELECT me.id, me.v FROM dict.forenames me WHERE ( ( ( me.v = ? ) AND (
me.id IS NULL ) ) ): 'foo'
SELECT me.id, me.v FROM dict.families me WHERE ( ( ( me.v = ? ) AND (
me.id IS NULL ) ) ): 'baz'
BEGIN WORK
INSERT INTO dict.families (id, v) VALUES (?, ?): 'NULL', 'bar'
DBI Exception: DBD::Pg::st execute failed: ERROR:  null value in
column "id" violates not-null constraint
 [for Statement "INSERT INTO dict.families (id, v) VALUES (?, ?)" with
ParamValues: 1=undef, 2='bar'] at
/home/justin/local/share/perl/5.8.8/DBIx/Class/Schema.pm line 940
        DBIx::Class::Schema::throw_exception('Tranquility::DB=HASH(0x852e694)',
'DBI Exception: DBD::Pg::st execute failed: ERROR:  null value...')
called at /home/justin/local/share/perl/5.8.8/DBIx/Class/Storage.pm
line 121
        DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Pg=HASH(0x8616104)',
'DBI Exception: DBD::Pg::st execute failed: ERROR:  null value...')
called at /home/justin/local/share/perl/5.8.8/DBIx/Class/Storage/DBI.pm
line 833
        DBIx::Class::Storage::DBI::__ANON__('DBD::Pg::st execute
failed: ERROR:  null value in column "id"...',
'DBI::st=HASH(0x894f964)', 'undef') called at
/home/justin/local/share/perl/5.8.8/DBIx/Class/Storage/DBI.pm line 990
        DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::Pg=HASH(0x8616104)',
'DBI::db=HASH(0x8887ce0)', 'insert', 'ARRAY(0x8955460)',
'DBIx::Class::ResultSource::Table=HASH(0x852df44)', 'undef',
'HASH(0x89553a0)') called at
/home/justin/local/share/perl/5.8.8/DBIx/Class/Storage/DBI.pm line 570
        DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Pg=HASH(0x8616104)',
'CODE(0x87d7cd8)', 'insert', 'ARRAY(0x8955460)',
'DBIx::Class::ResultSource::Table=HASH(0x852df44)', 'undef',
'HASH(0x89553a0)') called at
/home/justin/local/share/perl/5.8.8/DBIx/Class/Storage/DBI.pm line
1000
        DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::Pg=HASH(0x8616104)',
'insert', 'ARRAY(0x8955460)',
'DBIx::Class::ResultSource::Table=HASH(0x852df44)', 'undef',
'HASH(0x89553a0)') called at
/home/justin/local/share/perl/5.8.8/DBIx/Class/Storage/DBI.pm line
1009
        DBIx::Class::Storage::DBI::insert('DBIx::Class::Storage::DBI::Pg=HASH(0x8616104)',
'DBIx::Class::ResultSource::Table=HASH(0x852df44)', 'HASH(0x89553a0)')
called at /home/justin/local/share/perl/5.8.8/DBIx/Class/Row.pm line
210
        DBIx::Class::Row::insert('Tranquility::DB::Dict::Families=HASH(0x8954d1c)')
called at /home/justin/local/share/perl/5.8.8/DBIx/Class/Row.pm line
204
        DBIx::Class::Row::insert('Tranquility::DB::Asm::Names=HASH(0x8809414)')
called at /home/justin/local/share/perl/5.8.8/DBIx/Class/ResultSet.pm
line 1615
        DBIx::Class::ResultSet::create('DBIx::Class::ResultSet=HASH(0x88092d0)',
'HASH(0x880939c)') called at DB.pl line 13
ROLLBACK

=== SQL ===

CREATE TABLE dict.forenames (
    id  SERIAL  PRIMARY KEY,
    v   TEXT
);

CREATE TABLE dict.families (
    id  SERIAL  PRIMARY KEY,
    v   TEXT
);

CREATE TABLE asm.names (
    id    SERIAL  PRIMARY KEY,
    first   INT REFERENCES dict.forenames (id),
    middle  INT REFERENCES dict.families (id),
    last    INT REFERENCES dict.families (id)
);

=== DBIC ===

package Tranquility::DB::Dict::Forenames;

use strict;
use base qw(DBIx::Class);

__PACKAGE__->load_components(qw[Core]);
__PACKAGE__->table('dict.forenames');
__PACKAGE__->add_columns(qw[id v]);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many(first_names => 'Tranquility::DB::Asm::Names', 'first');

1;

package Tranquility::DB::Dict::Families;

use strict;
use base qw(DBIx::Class);

__PACKAGE__->load_components(qw[Core]);
__PACKAGE__->table('dict.families');
__PACKAGE__->add_columns(qw[id v]);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many( middle_names => 'Tranquility::DB::Asm::Names', 'middle');
__PACKAGE__->has_many( last_names => 'Tranquility::DB::Asm::Names', 'last');

1;

package Tranquility::DB::Asm::Names;

use strict;
use base qw(DBIx::Class);

__PACKAGE__->load_components(qw[Core]);
__PACKAGE__->table('asm.names');
__PACKAGE__->add_columns(qw[id first middle last]);
__PACKAGE__->set_primary_key(qw[id]);
__PACKAGE__->belongs_to(first_name =>
'Tranquility::DB::Dict::Forenames', 'first');
__PACKAGE__->belongs_to(middle_name =>
'Tranquility::DB::Dict::Families', 'middle');
__PACKAGE__->belongs_to(last_name => 'Tranquility::DB::Dict::Families', 'last');

1;

=== Test Script ===

#!/usr/bin/perl

package Tranquility::DB;

use strict;
use warnings;
use lib '/home/justin/projects/tranquility.app/lib';
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes();
my $schema = __PACKAGE__->connect('dbi:Pg:dbname=tranquility;host=localhost',
'tranquility', 'xxxx');

$schema->resultset('Asm::Names')->create( {
        first_name => { v => 'foo' },
        middle_name => { v => 'bar' },
        last_name => { v => 'baz' },
} );