Hi All,

I have an address table which has (among others) the columns COUNTRY_ID, STATE_ID, AREA_CODE and ZIP_CODE.
The following foreign keys apply:
COUNTRY_ID from the countries table
COUNTRY_ID+STATE_ID from the states table
COUNTRY_ID+AREA_CODE from the area codes table
COUNTRY_ID+ZIP_CODE from the zip-codes table

I have mapped entities for countries, states, area codes and zip codes.
Now I managed to map the address class successfully only when setting all my many-to-one relations to update="false" insert="false".
The data is read perfectly, but I need to update those properties as well. Naturally, when setting them they are not updated in the db.

I understand why it is problematic to update a column used in many foreign keys but probably there is some kind of paradigm to overcome this problem elegantly.

Thanks for your help

Two questions:
1. Can you be more specific, why you can't map Country_ID column, for example?
2. Is it possible for you to change the DB structure? Composite keys are rarely necessary, and i suppose your case could live quite happily w/o them.

Hi xor,

Thanks for your reply. Per your questions:
1.
I can't use the same column COUNTRY_ID in many composite ID mappings because NHibernate throws a mapping exception when I'm trying to do so telling me that I must use update="false" insert="false" when reusing the same column. This is no good because I need to be able to update those fields.

2.
Actually I can change the database structure, but I am reluctant to do so. IMO using natural candidate keys has many advantages compared to synthetic keys.
If I don't have a choice I might change the database structure but it's a bit unesthetic, I think, to change a valid and working database schema because you persistence tool can't cope with it well.
Nonetheless, I like NHibernate very much, don't take offence :lol:

Just so that everyone is clear, the Hibernate mantra has been that both surrogate keys and natural keys are supported, but surrogate keys are preferred. From the book "Hibernate in Action",

So we have opposing preferences. Big deal? Shouldn't be. But the difference is there. In extreme cases it becomes a bit like putting a round peg in a square hole, but for most cases (n)Hibernate should be able to work with most schemas.

Oh, i got it, drorwe. Of course, if you have several many-to-one relationships sharing one column, then NH don't want to give you ability to save object, because it afraids that it will not be able to complete.

Reasons obvious, i think. In the memory many-to-one link to the country looks the same as a link to a state - it's just a reference to object. There are no restrictions for you to set a "country" field to country A, and "state" field to the state, belonging to the country B. In this case NH will not be able to save your object.

Please note that this is my interpretation which could be different from official ideas of Hibernate architects.

P.S. I will not argue about natural keys, but on my experience they are almost always inconvenient. In fact, they are "natural" only for human brain, not for DBMS or O/R mappers. :)

xor & k-dub, thank you both for your replies.
I guess I will have to introduce surrogate keys where the natural-candidate key is composite.
Still, I plan to keep the natural keys in cases when the key is a single column. Anybody sees problem with that?

drorve, it depends, of course. :) But i'm sure you understand everything right.

Hi drorwe,

I have exactly the same problem. Did you find a way to go around it?

Thanks
Guillaume


PS : As far as I'm concerned, I'm allowed to change databases models so... no way to use surrogate keys unstead of composite ones.