Foreign Key Constraint Fails

[Gregg Bolinger]

I am getting "SQLException: Cannot add or update a child row: a foreign key constrant fails". And I have no clue why. I am using Hibernate3. If anyone can glance at this and let me know if they see anything that stands out, it would be much appreciated. Thanks. Below are relavent files:

<?xml version='1.0' encoding='utf-8' ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
    <class name="com.gthought.pnp.model.Item" table="t_item">
        <id name="itemId" type="int" column="item_id">
            <generator class="identity"/>
        </id>
 
        <property name="title" column="title" type="string"/>
        <property name="description" column="description" type="string"/>
        <property name="paypal" column="paypal" type="string"/>
 
        <set name="categories" table="link_item_category">
            <key column="item_id"/>
            <many-to-many class="com.gthought.pnp.model.Category" column="cat_id"/>
        </set>
 
        <many-to-one name="image" class="com.gthought.pnp.model.Image" column="image_id" />
        <many-to-one name="type" class="com.gthought.pnp.model.Type" column="type_id"/>
    </class>
</hibernate-mapping>

<?xml version='1.0' encoding='utf-8' ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
    <class name="com.gthought.pnp.model.Category" table="t_category">
        <id name="catId" type="int" column="cat_id">
            <generator class="identity"/>
        </id>
 
        <property name="description" column="description" type="string"/>
        <property name="cost" column="cost" type="double"/>
 
    </class>
</hibernate-mapping>

<?xml version='1.0' encoding='utf-8' ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
    <class name="com.gthought.pnp.model.Type" table="t_type">
        <id name="typeId" type="int" column="type_id">
            <generator class="identity"/>
        </id>
 
        <property name="description" column="description" type="string"/>
 
    </class>
</hibernate-mapping>

<?xml version='1.0' encoding='utf-8' ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
    <class name="com.gthought.pnp.model.Image" table="t_image">
        <id name="imageId" type="int" column="image_id">
            <generator class="identity"/>
        </id>
 
        <property name="imageName" column="image_name" type="string"/>
 
    </class>
</hibernate-mapping>

This is the SQL that is used to create the database:

create table link_item_category (
    item_id integer not null,
    cat_id integer not null,
    primary key (item_id, cat_id)
);
create table t_category (
    cat_id integer not null auto_increment,
    description varchar(255),
    cost double precision,
    primary key (cat_id)
);
create table t_image (
    image_id integer not null auto_increment,
    image_name varchar(255),
    primary key (image_id)
);
create table t_item (
    item_id integer not null auto_increment,
    title varchar(255),
    description varchar(255),
    paypal varchar(255),
    image_id integer,
    type_id integer,
    primary key (item_id)
);
create table t_type (
    type_id integer not null auto_increment,
    description varchar(255),
    primary key (type_id)
);
alter table link_item_category 
    add index FK6A0C3D257F5D4E0A (item_id), 
    add constraint FK6A0C3D257F5D4E0A 
    foreign key (item_id) 
    references t_item (item_id);
alter table link_item_category 
    add index FK6A0C3D2568F89852 (cat_id), 
    add constraint FK6A0C3D2568F89852 
    foreign key (cat_id) 
    references t_category (cat_id);
alter table t_item 
    add index FKCB5E5BFEF2D83CAA (image_id), 
    add constraint FKCB5E5BFEF2D83CAA 
    foreign key (image_id) 
    references t_image (image_id);
alter table t_item 
    add index FKCB5E5BFECE6688AA (type_id), 
    add constraint FKCB5E5BFECE6688AA 
    foreign key (type_id) 
    references t_type (type_id);

And this is the test code used to add a new record:

Set catList = new HashSet();
            catList.add(new Category(new Integer(1), null, null));
            catList.add(new Category(new Integer(2), null, null));
 
            Item item = new Item(null, 
                                 "Some Title", 
                                 "This is a pretty cool picture", 
                                  catList, 
                                  new Image(1, null), 
                                  new Type(1, null), 
                                  "paypal text stuff here");
 
            itemService.insert(item);

[Mark Spritzler]

Can you turn on logging and get the SQL statement that is being sent to the database? Which database, because you could probebly try running the exact statement directly to the database as gett a more specific reason, depending on database.

Mark

[Gregg Bolinger]

Originally posted by Mark Spritzler:
Can you turn on logging and get the SQL statement that is being sent to the database? Which database, because you could probebly try running the exact statement directly to the database as gett a more specific reason, depending on database.

Mark

This is the statement:

SQL [insert into t_item (title, description, image_id, type_id, paypal) values (?, ?, ?, ?, ?)]

I'll try running it against the database directly and see what happens.

[Gregg Bolinger]

Trying to run it against MySQL directly gives me the exact same error message.

[Gregg Bolinger]

Ok, everyone can slap me in the head. It was failing because I was inserting a value for image_id and the t_image table didn't have any data yet. Ugh! It's been a long week. Thanks for the help.

[Mark Spritzler]

Originally posted by Gregg Bolinger:
Ok, everyone can slap me in the head. It was failing because I was inserting a value for image_id and the t_image table didn't have any data yet. Ugh! It's been a long week. Thanks for the help.

I like the SQL statement with the ? marks, I meant the actual SQL statement executed. But anyway, I was leaning towards the Image, but I think the key is how you define certain relationships. Isn't there a difference between composite and association relationships, and what would automatically create records in the child tables for you if it doesn't exist. Kind of like walking the relationships.

Anyway, congrats for the head butt.

Mark