Chris,

>>Your only option might be to delete it and reenter it. Some intelligent keys might even be generated (orderline number) in which case it does make a difference if it is incorrect. Another example might be generated articleno or debtorno. They do have a meaning outside the RDBMS but are not entered by the user. There are many identifyers that are system generated and are exported to have a meaning outside the database system (social security number, Driver licence number, Employee number, Debtor number, passport number). Though they are generated, they still are intelligent keys. I don't know the internal procedures related to all these sort of numbers, but they still are unlikely to be changed.
>
>I am not sure what you are saying when you say the only option may be to delete it and reenter it. Are you saying delete the parent record, with the intelligent key, and all child records? If so, not very attractive.

I'm talking about static intelligent keys here. You've ask what static intelligent keys are used for. I said, for cases where the key is not or highly unlikely to be changed. For dynamic intelligent key's you'll have to use cascading updates.

What I'm saying is that there i a difference between intelligent keys entered by
the user and intelligent keys genereted by the database. For example, lets take and invoice table (cantaining the header info, not the invoice lines). What do you take as primary key ?

a. An unmeaningfull surrogate number ?
b. A generated invoive number which is printed on the invoice itself ?

Surrogate key purists would choose A. I would choose B. Why ? As to our fincancial guidelines, invoicenumbers when genereted should never change. The only difference between A and B is that the PK in case B has a meaning outside the database (in thus is an intelligent key by definion).

>Either way, these "intelligent" keys are not guaranteed to be unique, at least with the examples you have given.

Don't confuse intelligent keys with keys entered by the user. Intelligent keys can also be generated.

In other cases where the intelligent key *is* entered by the user it might change as long as there are no ristricting RI childs (an articleno might change as longs as it is not used in orderlines yet or occurs in important statistics).

>I think you are missing my point. It isn't a question of setting up the backend correctly. It is treating my data, whether it be SQL Server, Oracle, or whatever, as consistenly as possible. With intelligent keys and cascading updates, I have given an example of where the backends are inconsistent.

No, it's about setting up your database right. You'll have to setup your RI rules for cascading deletes anyways. Again I don't see the difference why it is a requirement to implement your cascading deletes right and forget to correctly implement cascading updates ? Tell me what's the difference ?????

>>Tell me what happens in the following story: You've got designed a database in SQL server designed for purposes trough a company or even an enterprise. Another 'developer' or poweruser makes a connection to the database and changes the surrogate key (how did I know I was not allowed to change it ???). You may say, well he should not be allowed to do so, but since it is possible and a surrogate key is stored in a normal field, this is a potential problem and danger.

>Can I not create an UPDATE trigger that is relatively consistent across my multiple backends to prevent this?

hmmmm, why not implement RI restrict rules ? Note that if you do, you're awfully close to implement cascading updates for the table. The point is that in fact you're implementing RI (Referential Integrity) rules. If you do this, then do it right !

If you look at it carefully there are not that many differences. For a surrogate primary key or static intelligent Primary key:
- You'll have to implement cascading deletes (RI rule)
- Prevent the change of the surrogate key accidently from outside, (can be done with RI Restrict rules)

With dynamic intelligent primary key:
- You'll have to implement cascading deletes (RI rule)
- Implement cascading updates (RI rule)

I don't think the former is significantly better than the latter.

>The same danger applies to your system. If a developer changes a social security number incorrectly, you still have bad data.

With one big difference, though the social security number is incorrect, it cascades the change trough all tables and the Primary Key and Foreign Key relation remain intact. In your case the database is leaved inconsistent because it now contains orphan rows as certain child records don't have a corresponding parent. If you'll examine this case carefully, you'll notice that this problem will not arise with intelligent keys which changes are cascaded trouh all related tables.

>Please do not misquote me. I have neved argued that I do not delete data due to poor data design. I generally do not delete data because in my experience, if someone took the time to put it in, they may need it back at some point. Even if a customer is no longer in business, I may need that customer and their order history. Disk space is cheap. Lost data is not.

Can you point out some reasons why the costumer want to undelete a record ? Would a backup not be sufficient ? What happens to performance when not deleting data ?

Walter,