On August 13th, 2006 Todd Farmer (not verified) says:
Yes - I use a surrogate auto-increment (with MySQL - sequences with Oracle) primary key value on almost every database table. It's critical that ORM tools be able to define row identity. Sometimes, that is a function of unique constraints of the data, and other times, it is not. It is true that Hibernate and other tools provide support for composite primary keys for establishing identity, but the support is extremely unwieldy. My experience has been that it is not hard to get Hibernate to recognize composite keys for identity purposes, but it's downright painful to try to define object relationships using these composite keys.
My rules for defining tables in my application are:
1. Always define a primary key.
2. The primary key must be a surrogate key with no business value.
3. Never use a composite primary key.
I also try to always use numeric data types for primary key columns, where practical. There may be instances where I don't do that (say in a list of US State Codes), but it's rare.
The above tends to lead me to use auto-increment columns more often than not. They also correspond to ORM-specific issues that have hurt me in the past. If you look at the Hibernate forums, for example, you'll find all sorts of problems related to composite keys - most answered by the Hibernate development team with comments like, "fix your data model - it's broken." Whether we agree with that or not, composite keys are a major handicap in dealing with ORM tools (in my experience).
Yes - I use a surrogate
Yes - I use a surrogate auto-increment (with MySQL - sequences with Oracle) primary key value on almost every database table. It's critical that ORM tools be able to define row identity. Sometimes, that is a function of unique constraints of the data, and other times, it is not. It is true that Hibernate and other tools provide support for composite primary keys for establishing identity, but the support is extremely unwieldy. My experience has been that it is not hard to get Hibernate to recognize composite keys for identity purposes, but it's downright painful to try to define object relationships using these composite keys.
My rules for defining tables in my application are:
1. Always define a primary key.
2. The primary key must be a surrogate key with no business value.
3. Never use a composite primary key.
I also try to always use numeric data types for primary key columns, where practical. There may be instances where I don't do that (say in a list of US State Codes), but it's rare.
The above tends to lead me to use auto-increment columns more often than not. They also correspond to ORM-specific issues that have hurt me in the past. If you look at the Hibernate forums, for example, you'll find all sorts of problems related to composite keys - most answered by the Hibernate development team with comments like, "fix your data model - it's broken." Whether we agree with that or not, composite keys are a major handicap in dealing with ORM tools (in my experience).
My $0.02.
Todd