i'm not sure why this is failing. it is obviously because of the constraint but i don't know how to fix it. any help would be great. thanks

__OUT__

swilson@swilson-mbp-vdebian:~/perl/test$ DBIC_TRACE=1 ./test.pl 
BEGIN WORK
SELECT me.pk, me.data_fk FROM main me WHERE ( ( me.data_fk IS NULL AND
+ me.pk IS NULL ) ): 
SELECT me.d_pk, me.some_dat, me.more_dat FROM data me WHERE ( ( me.mor
+e_dat = ? AND me.some_dat = ? ) ): 'more blah', 'blah'
SELECT me.d_pk, me.some_dat, me.more_dat FROM data me WHERE ( ( me.mor
+e_dat = ? AND me.some_dat = ? ) ): 'more blah', 'blah'
INSERT INTO data ( more_dat, some_dat) VALUES ( ?, ? ): 'more blah', '
+blah'
INSERT INTO main ( data_fk) VALUES ( ? ): '8'
ROLLBACK
DBI Exception: DBD::mysql::st execute failed: Cannot add or update a c
+hild row: a foreign key constraint fails (`test`.`main`, CONSTRAINT `
+main_info1` FOREIGN KEY (`pk`) REFERENCES `info1` (`info1_pk`)) [for 
+Statement "INSERT INTO main ( data_fk) VALUES ( ? )" with ParamValues
+: 0='8'] at /usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 10
+68
        DBIx::Class::Schema::throw_exception('TEST::Schema=HASH(0x1107
+b98)', 'DBI Exception: DBD::mysql::st execute failed: Cannot add or u
+...') called at /usr/local/share/perl/5.10.1/DBIx/Class/Storage.pm li
+ne 119
        DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::D
+BI::mysql=HASH(0x19dba78)', 'DBI Exception: DBD::mysql::st execute fa
+iled: Cannot add or u...') called at /usr/local/share/perl/5.10.1/DBI
+x/Class/Storage/DBI.pm line 1302
        DBIx::Class::Storage::DBI::__ANON__('DBD::mysql::st execute fa
+iled: Cannot add or update a child r...', 'DBI::st=HASH(0x160c488)', 
+undef) called at /usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.
+pm line 1627
        DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage:
+:DBI::mysql=HASH(0x19dba78)', 'DBI::db=HASH(0x1d99668)', 'insert', 'A
+RRAY(0x1f97968)', 'DBIx::Class::ResultSource::Table=HASH(0x112d5a0)',
+ 'HASH(0x1f979b0)', 'HASH(0x1f97b00)', undef) called at /usr/local/sh
+are/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 758
        DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::
+mysql=HASH(0x19dba78)', '_dbh_execute', 'insert', 'ARRAY(0x1f97968)',
+ 'DBIx::Class::ResultSource::Table=HASH(0x112d5a0)', 'HASH(0x1f979b0)
+', 'HASH(0x1f97b00)', undef) called at /usr/local/share/perl/5.10.1/D
+BIx/Class/Storage/DBI.pm line 1639
        DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI
+::mysql=HASH(0x19dba78)', 'insert', 'ARRAY(0x1f97968)', 'DBIx::Class:
+:ResultSource::Table=HASH(0x112d5a0)', 'HASH(0x1f979b0)', 'HASH(0x1f9
+7b00)', undef) called at /usr/local/share/perl/5.10.1/DBIx/Class/Stor
+age/DBI.pm line 1699
        DBIx::Class::Storage::DBI::insert('DBIx::Class::Storage::DBI::
+mysql=HASH(0x19dba78)', 'DBIx::Class::ResultSource::Table=HASH(0x112d
+5a0)', 'HASH(0x16c5c50)') called at /usr/local/share/perl/5.10.1/DBIx
+/Class/Row.pm line 349
        DBIx::Class::Row::insert('TEST::Schema::Result::Main=HASH(0x15
+b96a8)') called at /usr/local/share/perl/5.10.1/DBIx/Class/ResultSet.
+pm line 2636
        DBIx::Class::ResultSet::create('DBIx::Class::ResultSet=HASH(0x
+1d36d38)', 'HASH(0x1d44f58)') called at /usr/local/share/perl/5.10.1/
+DBIx/Class/ResultSet.pm line 2701
        DBIx::Class::ResultSet::find_or_create('DBIx::Class::ResultSet
+=HASH(0x1d36d38)', 'HASH(0x1d44f58)') called at ./test.pl line 16
        main::__ANON__() called at /usr/local/share/perl/5.10.1/DBIx/C
+lass/Storage/DBI.pm line 812
        DBIx::Class::Storage::DBI::__ANON__() called at /usr/local/sha
+re/perl/5.10.1/Try/Tiny.pm line 76
        eval {...} called at /usr/local/share/perl/5.10.1/Try/Tiny.pm 
+line 67
        Try::Tiny::try('CODE(0x1d36a80)', 'Try::Tiny::Catch=REF(0x19db
+490)') called at /usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.
+pm line 825
        DBIx::Class::Storage::DBI::txn_do(undef, undef) called at /usr
+/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 661
        DBIx::Class::Schema::txn_do('TEST::Schema=HASH(0x1107b98)', 'C
+ODE(0x1c41680)') called at ./test.pl line 28


__test.pl__

#!/usr/bin/perl

use strict;
use warnings;

use lib './lib';
use TEST::Schema;

my $schema = TEST::Schema->connect( 'dbi:mysql:database=test;host=192.
+168.15.222',
                                       'user',
                                       'pass');

my $main = $schema->resultset( 'Main' );

$schema->txn_do( sub {
   $main->find_or_create( {
      data =>     {
         some_dat => 'blah',
         more_dat => 'more blah'
      }, info1 => {
         info1_1 =>  'unique blah 1',
         info1_2 =>  'unique blah 2'
      }, info2 => {
         info2_1 =>  'more ublah 1',
         info2_2 =>  'more ublah 2'
      }
   } );
} );


__Main.pm__

package TEST::Schema::Result::Main;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("main");

__PACKAGE__->add_columns(
  "pk",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "data_fk",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
);
__PACKAGE__->set_primary_key("pk");

__PACKAGE__->might_have(
  info1 =>
  "TEST::Schema::Result::Info1",
  { "foreign.info1_pk" => "self.pk" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->might_have(
  info2 =>
  "TEST::Schema::Result::Info2",
  { "foreign.info2_pk" => "self.pk" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->belongs_to(
  data =>
  "TEST::Schema::Result::Data",
  { 'foreign.d_pk' => "self.data_fk" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE"
+ },
);

1;


__Data.pm__

package TEST::Schema::Result::Data;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("data");

__PACKAGE__->add_columns(
  "d_pk",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "some_dat",
  { data_type => "varchar", is_nullable => 1, size => 20 },
  "more_dat",
  { data_type => "varchar", is_nullable => 1, size => 20 },
);
__PACKAGE__->set_primary_key("d_pk");

__PACKAGE__->has_many(
  main =>
  "TEST::Schema::Result::Main",
  { "foreign.data_fk" => "self.d_pk" },
  { cascade_copy => 0, cascade_delete => 0 },
);

1;


__Info1.pm__

package TEST::Schema::Result::Info1;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("info1");

__PACKAGE__->add_columns(
  "info1_pk",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "info1_1",
  { data_type => "varchar", is_nullable => 1, size => 20 },
  "info1_2",
  { data_type => "varchar", is_nullable => 1, size => 20 },
);
__PACKAGE__->set_primary_key("info1_pk");


__PACKAGE__->belongs_to(
  main =>
  "TEST::Schema::Result::Main",
  { 'foreign.pk' => "self.info1_pk" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE"
+ },
);

1;


__Info2.pm__

package TEST::Schema::Result::Info2;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("info2");

__PACKAGE__->add_columns(
  "info2_pk",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_foreign_key => 1,
    is_nullable => 0,
  },
  "info2_1",
  { data_type => "varchar", is_nullable => 1, size => 20 },
  "info2_2",
  { data_type => "varchar", is_nullable => 1, size => 20 },
);
__PACKAGE__->set_primary_key("info2_pk");

__PACKAGE__->belongs_to(
  main =>
  "TEST::Schema::Result::Main",
  { 'foreign.pk' => "self.info2_pk" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE"
+ },
);

1;


__MySQL__

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| data           |
| info1          |
| info2          |
| main           |
+----------------+
4 rows in set (0.00 sec)

mysql> show create table data;
+-------+-----------------+
| Table | Create Table 
|
+-------+-----------------+
| data  | CREATE TABLE `data` (
  `d_pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `some_dat` varchar(20) DEFAULT NULL,
  `more_dat` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`d_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------+
1 row in set (0.00 sec)

mysql> show create table info1;
+-------+-----------------+
| Table | Create Table 
|
+-------+-----------------+
| info1 | CREATE TABLE `info1` (
  `info1_pk` int(10) unsigned NOT NULL,
  `info1_1` varchar(20) DEFAULT NULL,
  `info1_2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`info1_pk`),
  CONSTRAINT `info1_main` FOREIGN KEY (`info1_pk`) REFERENCES `main` (
+`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------+
1 row in set (0.00 sec)


mysql> show create table info2;
+-------+-----------------+
| Table | Create Table 
|
+-------+-----------------+
| info2 | CREATE TABLE `info2` (
  `info2_pk` int(10) unsigned NOT NULL,
  `info2_1` varchar(20) DEFAULT NULL,
  `info2_2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`info2_pk`),
  CONSTRAINT `info2_main` FOREIGN KEY (`info2_pk`) REFERENCES `main` (
+`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------+
1 row in set (0.00 sec)

mysql> show create table main;
+-------+-----------------+
| Table | Create Table 
|
+-------+-----------------+
| main  | CREATE TABLE `main` (
  `pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data_fk` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `main_data` (`data_fk`),
  CONSTRAINT `main_info2` FOREIGN KEY (`pk`) REFERENCES `info2` (`info
+2_pk`),
  CONSTRAINT `main_data` FOREIGN KEY (`data_fk`) REFERENCES `data` (`d
+_pk`),
  CONSTRAINT `main_info1` FOREIGN KEY (`pk`) REFERENCES `info1` (`info
+1_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------+
1 row in set (0.00 sec)

[download]

In reply to dbic insert into related tables by ag4ve

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.