>We seem to have different interpretations on this. I do regard a unique constraint as a part of the RM, you don't. May I ask how you would enforce this business rule ??

>snip

>I don't reject the use of generated keys, I reject the forced use of generated keys in all situations. Therefore I'll not have any problem in maintaining or enhancing that system. Situations where generated PKs are not possible (History Problem, PKs on ranges) the problem does not occur because they can't be solved with generated PKs (nor relational theory).

There are two sets of constraints on the attributes of an entity, those of the attribute's domain and those of the keys. Keys are part of the relational rules, domains are part of the business analysis. Just because an attribute's domain requires uniqueness does not cause that attribute to be a good candidate for the PK. Since the PK has responsibilities beyond the entity in which it appears (FKs in relationships) there are other things to consider in selecting the PK, among which is included the likliness of its value changing over time. A surrogate key never changes over time, so it has no downside in that area. Natural keys may or may not change over time, and if they may then the possibility of its change must be addressed.

Business rules are best enforced through the business logic layer of an application and not through the database integrity rules. This is because over time business rules may change but database integrity rules do not change over time.

A simple example: A customer table using the CustNumber (AlphaNumeric) as the PK. A major customer is ACME Corporation. The users decide they want that CustNumber to be ACME01 so it is easy to remember. 10 years go by and hundreds of invoices and payments have been entered for ACME. One day ACME is bought by ZENITH. The users want the CustNumber to be ZENITH01 so ti will remain easy to remember. With surrogate keys this is easy to accomodate without any mass updates to invoices or payments, with the nautral key the mass updates are needed.

Please, keep in ind, that for me a surrogate key is NEVER seen or used by a human. The humans still use the CustNumber to find the customer. The relationships with other tables are based on the surrogate. So in the scenario above I just let the users change the CustNumber and insure the uniqueness within the table. I have no need to involve myself with any other tables as they do not use the CustNumber at all.

My choice to uniformly use surrogates is simply to provide consistency across all tables.