I can't figure out how to set up the mapping for a many-to-many relation when the many table in my database has foreign keys. What I want is a many-to-many bidirectional relation between User and Role.
The problem is that I get a exception (see below) with the message "Cannot delete or update a parent row: a foreign key constraint fails" when I try to delete a Role. Actually I can get it to work by removing inverse="true" from the Role mapping below, but I don't know if that is the correct solution.
The documentation says (8.7) that "You may now delete objects in any order you like, without risk of foreign key constraint violations."
Any ideas on how to solve this?
Exception:
Code:
Hibernate: select role0_.role_id as role_id0_, role0_.title as title0_, role0_.description as descript3_0_, role0_.permanent as permanent0_ from roles role0_ where role0_.role_id=?
Hibernate: select acl0_.id as id__, acl0_.role_id_fk as role_id_fk__, mediacolle1_.collection_id as collecti1_0_, mediacolle1_.is_private as is_private0_, mediacolle1_.description as descript3_0_, mediacolle1_.title as title0_, acl0_.id as id1_, acl0_.permission as permission1_, acl0_.collection_id_fk as collecti3_1_, acl0_.role_id_fk as role_id_fk1_ from acl acl0_ left outer join collection mediacolle1_ on acl0_.collection_id_fk=mediacolle1_.collection_id where acl0_.role_id_fk=?
Hibernate: delete from roles where role_id=?
2004-01-13 08:14:40,796 WARN [ThreadPoolWorker:Thread-12] (@) (JDBCExceptionReporter.java:38) - SQL Error: 1217, SQLState: S1000
2004-01-13 08:14:40,796 ERROR [ThreadPoolWorker:Thread-12] (@) (JDBCExceptionReporter.java:46) - General error, message from server: "Cannot delete or update a parent row: a foreign key constraint fails"
2004-01-13 08:14:40,796 WARN [ThreadPoolWorker:Thread-12] (@) (JDBCExceptionReporter.java:38) - SQL Error: 1217, SQLState: S1000
2004-01-13 08:14:40,843 ERROR [ThreadPoolWorker:Thread-12] (@) (JDBCExceptionReporter.java:46) - General error, message from server: "Cannot delete or update a parent row: a foreign key constraint fails"
2004-01-13 08:14:40,859 ERROR [ThreadPoolWorker:Thread-12] (@) (JDBCException.java:38) - Could not execute JDBC batch update
java.sql.BatchUpdateException: General error, message from server: "Cannot delete or update a parent row: a foreign key constraint fails"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1469)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68)
at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$e90d3943.executeBatch(<generated>)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:118)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2311)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2266)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
Here is the DDL that I use (MySQL 4.0.x, InnoDB):
Code:
CREATE TABLE user_role (
user_role_id INT NOT NULL AUTO_INCREMENT,
username_fk VARCHAR(200) NOT NULL,
role_id_fk INT NOT NULL,
PRIMARY KEY (user_role_id),
INDEX username_fk_index(username_fk),
INDEX role_id_index(role_id_fk),
FOREIGN KEY (username_fk) REFERENCES users (username),
FOREIGN KEY (role_id_fk) REFERENCES roles (role_id)
)
CREATE TABLE roles (
role_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
description VARCHAR(200),
permanent BOOL NOT NULL,
PRIMARY KEY (role_id),
UNIQUE unique_roles_title_index (title)
);
CREATE TABLE users (
username VARCHAR(200) NOT NULL,
password VARCHAR(200) NOT NULL,
first_name VARCHAR(200) NOT NULL,
last_name VARCHAR(200) NOT NULL,
private_collection INT,
permanent BOOL NOT NULL,
PRIMARY KEY (username)
);
And the mapping for User and Role
Code:
<hibernate-mapping>
<class name="User" table="users">
<id name="username" column="username">
<generator class="assigned"/>
</id>
<property name="firstname" column="first_name"/>
<property name="lastname" column="last_name"/>
<property name="password" column="password"/>
<property name="permanent" column="permanent" type="boolean"/>
<many-to-one name="privateCollection" class="MediaCollection" column="private_collection"
unique="true"/>
<bag name="role" table="user_role" lazy="true">
<key column="username_fk"/>
<many-to-many column="role_id_fk" class="Role"/>
</bag>
</class>
</hibernate-mapping>
...
<hibernate-mapping>
<class name="Role" table="roles">
<id name="id">
<column name="role_id"/>
<generator class="native"/>
</id>
<property name="title" column="title" type="string"/>
<property name="description" column="description" type="string"/>
<property name="permanent" column="permanent" type="boolean"/>
<bag name="user" table="user_role" lazy="true" inverse="true">
<key column="role_id_fk"/>
<many-to-many column="username_fk" class="User"/>
</bag>
</class>
</hibernate-mapping>
Thanks
Per Thomas
Login
FAQ
Search
