Hibernate Many-to-Many mapping not working correctly on insert
I have inherited a Spring+NHibernate c# app that we are doing a major overhaul on (and I'm fairly new to NHibernate). The user/role set up in the database was less than optimal so I'm rewriting it since part of the project was adding new functionality anyway. However, the translation into NHibernate isn't working correctly. Here's the relevant parts of my setup:
Database: 3 tables; USERS, ROLES and USERS_XREF_ROLES (which is the join/bridge table)
User.hbm.xml:
<set name="Roles" table="[USERS_XREF_ROLES]" lazy="false" order-by="[FK_ROLES] asc" cascade="all">
<key column="[FK_USERS]" />
<many-to-many class="UserRole" column="[FK_ROLES]" />
</set>
UserRole.hbm.xml:
<set name="Users" inverse="true" table="[USERS_XREF_ROLES]" lazy="false" >
<key column="[FK_ROLES]" />
<many-to-many class="User" column="[FK_USERS]" />
</set>
Also note, there wasn't originally a set "Users" in the UserRole class; I had added it due to adding the bridge/join table and another post on SO that mentioned using a reverse set for bridge/join tables. All SELECTs were working fine. I went through multiple users and tested the permissions they should have. However, when I tried to create a new user, it was obviously trying to insert into ROLES as well:
Error: Hibernate operation: could not insert: [UserRole][SQL: INSERT INTO [ROLES] ([VALUE]) VALUES (?); select SCOPE_IDENTITY()]; uncategorized DataException for SQL [INSERT INTO [ROLES] ([VALUE]) VALUES (?); select SCOPE_IDENTITY()]; ErrorCode []; Cannot insert the value NULL into column 'NAME', table 'ROLES'; column does not allow nulls. INSERT fails. The statement has been terminated.
A new user should obviously generate an INSERT into USERS and USERS_XREF_ROLES, but NOT ROLES. So, what am I doing incorrectly here? Thanks.
UPDATE: I'm trying to make my question clearer. My big problem is that all the examples I've ever found show inserting BOTH of the related objects into eac开发者_如何学Pythonh respective table and into the join table. I want to be able to add a User with an EXISTING Role. I don't ever want an insert to occur in the ROLES table when creating a User. Creating a User should always insert ONE record into USERS and one or more records into USERS_XREF_ROLES, but NEVER into ROLES because the user will always be assigned to existing roles. I hope this is clearer. I have yet to find an example that shows this and this must be a pretty common thing to do.
A new user should obviously generate an INSERT into USERS and USERS_XREF_ROLES, but NOT ROLES. So, what am I doing incorrectly here? Thanks.
This statement confuses me and I may just be misunderstanding but if USERS_XREF_ROLES is a bridge table then why would an INSERT into USERS necessitate an insert into the bridge table? It is possible a user may exist with no roles assigned is it not? With that in mind I'd expect that the problem isn't with the nHibernate mapping but rather the way your c# objects are being instantiated. Perhaps an unbound Role class is being created with the instantiation of the User class?
精彩评论