aspose file tools*
The moose likes Object Relational Mapping and the fly likes What is the better choice for primary key ? natural key or system-generated key ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "What is the better choice for primary key ? natural key or system-generated key ?" Watch "What is the better choice for primary key ? natural key or system-generated key ?" New topic
Author

What is the better choice for primary key ? natural key or system-generated key ?

ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 370
By reading Hibernate books, I found that many suggest using some unique ID that is generated by the database system, e.g for MySQL it is AUTO_INCREAMENT. Some even say we should "avoid" using natural key 9one or more fields from the Table) and should always stick with system generated ID.

If I know which field is the primary key, is that really drawback or problem if I just use them as the primary key (setting generator="assigned") ?


thanks.
pascal betz
Ranch Hand

Joined: Jun 19, 2001
Posts: 547
it's more that you should not use a PK which has any business meaning, not if you or hibernate assigns it.

some people prefer self assigned PKs over hibernate assigned (e.g. because then you can use the id property for your equals method which is not possible if hibernate assigns them because they are only assigned when entity is saved). i prefer hibernate taking care of it (it's easier for me).
the key generation strategy used depends on the DB system and the mapping (e.g. some mapping class hierarchy mapping do not allow "identy" keys, check the manual)

no matter what you choose, you should prefer a nullable key (Integer, String, Long) over a primitive (int, long) because they dont need magic values for unsaved entities (e.g. "-1"). hibernate needs the unsaved value to determine if it should issue an "update" or "insert" statement for an entity.


just in case:
a PK with business meaning is bad because the business might change (e.g. if you choose email address as PK and then someone decides that a user might have two accounts...).


pascal
[ May 01, 2006: Message edited by: pascal betz ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The obvious downside to letting Hibernate manage Primary Key generation for entities in your schema is that not all applications which connect to that schema may do so through Hibernate.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 370
Originally posted by Paul Sturrock:
The obvious downside to letting Hibernate manage Primary Key generation for entities in your schema is that not all applications which connect to that schema may do so through Hibernate.


Exactly. That's what I just thought about. suppose you have two tables with one-to-one relation, you use "sequence" key generation method. Then what if

1) you are NOT going to use POJO to populate your database, your input file is some text file from business team so it does NOT include any oracle sequence value from DUAL table, how do you load these data into your table ?

2. When you use such sequence or AUTO_INCREAMENT as key, how can other application that doesn't use hibernate to retrieve things ? They use JDBC but they have no idea about what's the primary key value for a certain row.
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2906
Originally posted by ben oliver:
you are NOT going to use POJO to populate your database, your input file is some text file from business team so it does NOT include any oracle sequence value from DUAL table, how do you load these data into your table ?

In that case you tend to use SQL*LOADER (SQLLDR) to load the data. You can either use the SQLLDR SEQUENCE function to generate ids independent of the DB sequence OR if the database is online while you run SQLLDR you can even access the DB sequence (nextval) directly in the control file.
Alternately you can create a BEFORE INSERT row trigger that fills in NULL id columns with values from the sequence.

Originally posted by ben oliver:
When you use such sequence or AUTO_INCREMENT as key, how can other application that doesn't use hibernate to retrieve things?

Just because you are using a surrogate key doesn't mean that the record doesn't have a business domain key. Other applications will simply use the business domain key or any other relevant criteria in the WHERE clause of their SELECTs to find the appropriate records � they should still use the surrogate keys in the JOIN clause.


"Don't succumb to the false authority of a tool or model. There is no substitute for thinking."
Andy Hunt, Pragmatic Thinking & Learning: Refactor Your Wetware p.41
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 370
Peer, thanks for the SQLLDR for tables with surrogate key information.

1.What if there are two surrogate keys for a table ? For example, a table has its own PK and a FK pointing to another table's PK. In this case, you think there is no problem for SQLLDR to handle it ?

2. Let's think about a more realistic scenario --- First I use hibernate to save some data (say 1000 rows) into such SEQUENCE key oriented table, then two months later, somebody gives me a .txt file to update these 1000 rows with different data content and this person is from business group that only provides data for those "natural" fields, then this time if I use SQLLDR, the PK values for these 1000 rows will change. Doesn't that present any problem to us ?
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2906
Originally posted by ben oliver:
2. Let's think about a more realistic scenario --- First I use hibernate to save some data (say 1000 rows) into such SEQUENCE key oriented table, then two months later, somebody gives me a .txt file to update these 1000 rows with different data content and this person is from business group that only provides data for those "natural" fields, then this time if I use SQLLDR, the PK values for these 1000 rows will change. Doesn't that present any problem to us ?


Knowing Oracle I suspect that SQLLDR is quite powerful - its just a matter of getting familiar with it; it also tends to be used when tables are loaded for the first time. As I haven't used it extensively I would lean towards writing a program that reads in the text file and generates a SQL*Plus or PL/SQL script where the UPDATES use the business domain key for the initial selection but the surrogate keys for the JOINs.

You can also generate an intermediate query script so that you can separate the existing data from the new data (UPDATEs vs INSERTs).

If this is regular process I would probably write some if not all of it in
PL/SQL to automate it as much as possible.

(Unless of course the updates are of such a nature that the updated data causes a cascade of changes through the business model - e.g. the change of state of an Order affects the Order-Items in some way. In that case you would be better off by reusing parts of your application in a utility (or make it part of the application). Sharing code is preferable to duplicating business logic.)
[ May 03, 2006: Message edited by: Peer Reynders ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What is the better choice for primary key ? natural key or system-generated key ?
 
Similar Threads
cascading classes and tables (how to update ?)
how to get primary key after doing an insert
CMP and primary key
Hibernate Mapping problem because no primary key in database
what if no ID column in the table for Hibernate?