Many-to-many id problem

ulfreich · **Posted:** Fri Oct 15, 2004 12:12 pm

Hello,

I have a many-to-many relationship between the A and B classes, where the table in the middle is composed of its id, the id of one side (A class) and the id of the other (B class):

Table A_B:

ID integer not null (pk);
A_ID integer not null;
B_ID integer not null;

When I try to insert a B object into A's set of Bs,

The generated SQL (show_sql=true):
insert into A_B (A_ID, B_ID) values (?, ?)

I get a database error:

Code:

ORA-01400: cannot insert NULL into ("USER"."A_B"."ID")

because it is trying to insert the record without generating an ID..

Am I supposed to have just A_ID and B_ID in the A_B table, or can I let the ID column and have hibernate generate it when inserting a new record? Please help :S, thanks in advance!

Hibernate version:2.1.6

Mapping documents:

A.hbm.xml (xdoclet generated)

Code:

        <set
            name="Bs"
            table="A_B"
            lazy="false"
            inverse="false"
            cascade="save-update"
            sort="unsorted"
        >

              <key
                  column="A_ID"
              >
              </key>

              <many-to-many
                  class="B"
                  column="B_ID"
                  outer-join="auto"
               />

        </set>

Name and version of the database you are using:
Oracle 9i

(no "ID" anywhere)

david · **Posted:** Fri Oct 15, 2004 9:40 pm

You have not shown it but I assume you don't have an appropriate ID generator defined. Try a native generator first.

See:
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-declaration-id

ulfreich · **Posted:** Sat Oct 16, 2004 1:25 pm

Thanks so much for your reply David, I'm using "increment" for id generation in both A and B classes.. I didnt map the table in the middle (A_B) because it is not necessary, since hibernate controls the insertion of records in it. That's why I dont have an id generator for that table.. is there a way I can tell hibernate (in the "set" declaration) to generate an id and insert it in the A_B.ID? Or should I just remove the ID field from the table?

christian · **Posted:** Sat Oct 16, 2004 1:27 pm

You can use an <idbag> mapping with a suorrogate <collection-id> generator for the link table. Keep in mind that this model is not great: You not only allow many-to-many multiplicity, but one particular B could be associated with one particular A _several times_.

christian · **Posted:** Sat Oct 16, 2004 1:28 pm

In other words: better make this A_B table a full entity mapping to a new class and use two one-to-many instead of a many-to-many.

christian · **Posted:** Sat Oct 16, 2004 1:29 pm

Or add a unique key on the A_ID, B_ID composite columns.

ulfreich · **Posted:** Sat Oct 16, 2004 2:25 pm

Thanks guys, finally I've decided to drop the ID column and it works nicely, since I wasnt really using it.. nice to know the other alternatives, for when I have to store aditional information..