Chris,

>>So we agree that the FORCED use of surrogate keys is not wise in all circumstances ??
>
>I do not know what you mean by FORCED. I have yet to come across a case where I would not use surrogate keys.

Well, what about the case of invoice numbers ??? Are you consistent and add a surrogate key, even though a autonumber invoicenumber would do ? I'm not, because the surrogate key would add no benefit to the current scheme.

I do not like be forced to use surrogate keys. Let's say I use a framework that does not allow me to use surrogate keys and I accept a project where I have to work with an existing database containing intelligent keys. How would I solve this ? Saying: I cannot accept this job, because I cannot handle intelligent keys ??

>>In general consistency is good, but you'll have to watch out where consistency is driven too far. In this manner you might limit your options and create narrow view. The thing I'm merely saying is: "Before you add a surrogate key, just think about whether this is handy and/or required"

>I have yet to come across an example where consistency is driven too far. Do you have an example where it is?

Example of invoicenumbers.

>>>There is a big difference between implementing a trigger to prevent a surrogate key from changed across mulitple backends, to implementing cascading updates through RI.

>>Well, In VFP, Oracle and SQL server 2000 (As i recall correctly it supports declarative integrity) it is not, just specify your option.

>But SQL Server 7.0 does not, and that is what I am learning now. < g >

Are you sure there are no tools to define your RI rules. I just can't imagine this is the case.

>>These are implementation issue of one specific server. In VFP I can use the RI builder to take care of that. Writing business rules to prevent a column (other than the PK) from change if it has related records in child tables is more troublesome. lets take the following example:

>>A. Table: Articleno, Description, price, etc.

The articleno is an intelligent key and has RI ristrictions with child tables. Since you like to use surrogate keys you'll add one:

>>B. Table: Art_pk, Articleno, Description, Price, etc.

In case A I could easely prevent the articleno from changing if for example the articleno occurs in the sales table. In case B, I'll have to write your own triggers (business rule) to achieve the same. With the RI builder, in case A, I can setup these restrictions quite easely. In case B, it would be more troublesome to create such business rules.

>Yes, but this specific server is what I am working on. And I want to avoid, if possible, treating it differently than my other backends. Again, I want consistency.

I don't see why this is so different from other backends. Just implement the RI rules (for most systems you should apply delete RI rules anyways). Then the whole thing is transparent and consistent.

>>This depends on what RI rule is attached to it (Restrict, Ignore, Cascading). I'm not that familiar with SQL 7 to confirm that it automaticly adds a Restrict RI rule.
>
>It does not automagically add a RI rule. If I add a foreign key constraint to a table (and I do to make sure child records cannot be inserted as orphans), I cannot change the parent's primary key.

This behaviour *is* a RI restrict rule (for both the parent (update) and child (insert)).

>>>If I have a Customer table, with a surrogate key, and an Order table, with a surrogate key and the CompanyID as a foreign key, no one (developer or power user) can change the CompanyID in the Company table. SQL Server will not allow it.
>>
>>If correct (I cannot confirm this), this is a specific implementation that applies to SQL Server 7, and does not say that any other (R)DMBS behaves the same. VFP for example does not.

>But I prefer consistency, when available.

I don't see why this is inconsistend. Databases are implemented different in different RDBMSs. Datatypes, RI rules, businessrules and other constraints are implemented different accros (R)DMBSs. As long as the database behaves the same I don't see why there is no consistency.

>>>Now, someone can change the CompanyID field in the Order table, but how is this any different than your system. I could just as easily go to a child table and change the SSN.
>>
>>If the companyId entered in this field is an existing one, it does not matter for the database: it's leaved consistent. The point I was making is that when you'll change the surrogate keys, there should be RI rules active to prevent the database to become inconsistent (Foreign key has no corresponding Primary key)
>
>My point is still the same. If someone changes a value in either system, you still have bad data. It may be consistent, but it is still bad.

There is no way of knowing I the correction was from bad to good or good to bad. As long as the database is consistent the database is correct. You can't protect your database from users entering false data in either case.

>>You're drawing the wrong conclusions here. Please tell me, how is my example any different than changing the SSN in the SS table ??? If the system should prevent a SSN from changing then we would have applied a restrict RI rule (in your case you'll have to write a custom trigger) rather than a cascading RI rule. The point is, you'll should prevent a database to become inconsistent. During a transaction a database should be updated from one consistent state into another. If your database scheme allows a database to become incosistent, it is a defect in your database design (note, in the real world you won't come accros many perfectly designed databases).

>No, in your example, you are changing a PK value, in this case a SSN, which cascades throughout the child records. The database allows it, and the user goes on their merry way. Your database is consistent, in that you do not have any orphans, but the SSN is still incorrect. So either way, you still have bad data. Now, how are you going to find it?

I don't get it, how can the system possibly know if I entered incorrect data ? In the case of surrogate keys, exactly the same happens: If you change the SSN the system cannot possibly know if you changed it from bad to good or from good to bad. I guess you did not catch the point. Please, reread the issue.

>>I would say: When the new manager comes in, I'll take a backup and add the historical data. Another way would be to add a history table in which the data is put aside. Tell me, If you want to delete an order (because the client has redrawn), you mark it as inactive in case the client might rethink his redrawal ??

>Not so easy in SQL Server, at least as far as I know. Do you have any expericence retrieving a backup and integrating it with existing data in SQL Server?

Nope, but I can setup a second SQL server, restore the backup and append the old data to the new data. It cannot be that difficult.

Walter,