werner, there are two foreign keys in the stuff_and_things table: "stuff_and_things_stuff_id_fkey" FOREIGN KEY (stuff_id) REFERENCES stuff(id) "stuff_and_things_thing_id_fkey" FOREIGN KEY (thing_id) REFERENCES things(id)
also, for total completeness, we are using castor 1.3, our database is postgresql 8.1 and we are using postgresql-8.1-407.jdbc3.jar what is the expected behavior in the scenarios i laid out? -peter On Wed, Sep 2, 2009 at 1:06 AM, Werner Guttmann <[email protected]>wrote: > Peter, > > can you please highlight the (e.g. foreign key) constraints in your SQL > schema, just for completeness ? > > Regards > Werner > > peter cowan wrote: > > I have a pretty standard many-to-many relation mostly working, I can > create > > , load and update objects fine, and the database tables populate as > > expected, however when I try to remove objects I am running into trouble. > > Below is a database description, the mapping and some example code. > > > > database tables: > > > > table - stuff: > > id - int > > name - varchar > > > > table - things: > > id - int > > name - varchar > > > > table - stuff_and_things: > > id - int > > stuff_id - int > > thing_id - int > > > > <mapping> > > <class name="my.test.app.Stuff" identity="id"> > > <map-to table="stuff"/> > > <field name="id" type="int"> > > <sql name="id" type="integer" /> > > </field> > > <field name="name" type="string"> > > <sql name="name" type="varchar" /> > > </field> > > <field name="things" type="my.test.app.Thing" > > collection="arraylist"> > > <sql name="stuff_id" > > many-table="stuff_and_things" > > many-key="thing_id" /> > > </field> > > </class> > > > > <class name="my.test.app.Thing" identity="id"> > > <map-to table="things" /> > > <field name="id" type="int"> > > <sql name="id" type="integer" /> > > </field> > > <field name="name" type="string"> > > <sql name="name" type="varchar" /> > > </field> > > </class> > > </mapping> > > > > Both Stuff and Thing are simple Java Beans and nothing more. > > > > In the below example, the objects are created correctly in the first > > transaction, and loaded correctly in the second transaction. When I try > to > > remove a Thing from the ArrayList data member of Stuff, the Thing is > only > > removed from the relation table stuff_and_things, and an orphan is left > in > > the things table. This makes enough sense, however, when I try to then > > remove the Thing from the database by uncommenting > > 'database.remove(thing);', I get an exception that it can't be removed > due > > to foreign key constraints in stuff_and_things. This also makes sense, > but I > > am left wondering, what am I doing wrong? How do I go about deleting > those > > orphaned records without doing it in a second transaction? > > > > public static void testStuffAndThings() { > > Database database = jdoManager.getDatabase(); > > database.begin(); > > > > Thing thing = new Thing(1,"My Thing"); > > Stuff stuff = new Stuff(1,"My Stuff"); > > ArrayList things = new ArrayList(); > > things.add(thing); > > stuff.setThings(things); > > > > database.create(thing); > > database.create(stuff); > > database.commit(); > > > > database.begin(); > > stuff = (Stuff) database.load(Stuff.class, 1); > > things = stuff.getThings(); > > thing = things.remove(0); > > //database.remove(thing); > > database.commit(); > > database.close(); > > } > > > > thanks, > > -peter > > > > --------------------------------------------------------------------- > To unsubscribe from this list, please visit: > > http://xircles.codehaus.org/manage_email > > >
