>Chris,
>
>>>An intelligent key that can not be changed whenever it is entered or when it has child records attached to it.
>>
>>Can you give some examples of this? In examples given earlier by others, there always seems to be the possibility that an intelligent key is either incorrect (bad information) or entered incorrectly (data-entry error). Now what do I do?
>
>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.

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

>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).
>
>>>That's not what I meant. Each self-respecting (R)DBMS supports cascading, Restricts, Ingore or nullifies RI rules. How they're implemented is irrelevant: As long as i'm sure that when a PK is changed the RI rules are respected. If both a ORACLE and SQL server implemented the RI rules, it makes no difference for an attached VFP application to which server it's connected.
>
>>No, but if I am building both the VFP front end and the multiple backends, one backend hands the cascades for me, while one doesn't. If I use surrogate keys, I don't have to worry about it.
>
>You've got to be sure your back-end is setup right. If your RI rules are not setup right you might end up with ophan rows, even if you use surrogate keys. A poor designed and setup database is *never* (I said never) a excuse to use surrogate keys. If you do, your problems only might grow worse.

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.

If I use a surrogate key and the social security number for a record is incorrect, the method for changing that number is relativley consistent. I change the number in once place. With intelligent keys, my backend behave differently.

I think it was JVP who wrote that as developers, we crave consistency.

>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?

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

>>Yet in your example above, you cannot change an intelligent key. It seems as though this is a contradiction, unless I am misunderstanding you.
>
>In each database your RI rules must be setup right. If this is the case you can use intelligent keys safely. If you change them (and the RI rules allow you to), the foreign keys in the related tables are also changed. This is not any different that with deleting data: If you delete a parent (and the RI rules allow you to), all related records in child tables are deleted also.

See above.

>Poor design of your database may not ever be a reason to say: I don't delete data, I mark them as inactive. Surrogate keys migth help to mask poor database design but will not hide it. There will allways be problems (mainly with RI).

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.