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

[Jess Robinson]

On Thu, 29 Nov 2007, Justin Jereza wrote:

> 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;

You didn't tell DBIC that the "id" column was auto incrementing/serial at 
all.. supply some column info eg:
__PACKAGE__->add_columns( id => { is_auto_increment => 1, data_type => 
'serial' },
 	v => { data_type => 'varchar' } );
(or whatever types they actually are.

RTFM in DBIx::Class::ResultSource under add_columns for all the possible 
keys. Especially the is_auto_increment is important in your case.


> 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' },
> } );


Jess