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.

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.

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.

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

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).

Walter,