Chris,

First of all, HAPPY NEW YEAR to you !!

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

>I still disagree, based on the argument that I want consistency throughout my database. I can get that every time with surrogate keys. Indeed, I can also get a great degree of consistency in naming my surrogate keys. TableName + ID. Makes life wonderful when I write my middle-tier.

Though I tend to do this as well (at least for surrogate keys) in does not stop me for putting an intelligent key value in it.

>In your example, I may be able to use intelligent keys a lot of the time, but not all.

So we agree that the FORCED use of surrogate keys is not wise in all circumstances ??

>If I can treat all my tables throughout all my databases across all my backends in a consistent manner, I have a better, more maintainable system. Do you not look for consistency?

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"

>Yes, I use generated keys. I call them surrogate keys. < g >

Watch out. Generated keys can be intelligent keys (In general, invoice numbers are intelligent though generated), they certainly don't have to be surrogate keys (which have no meaning outside the database).

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

>On the system I am working on now, I don't delete. Period. And I don't have to worry about cascading updates because I use surrogate keys.

So your standpoint on this issue is based on this project ? I'm trying to make a general point here.

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

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

>As I have pointed out, and you have failed to address, I believe this I don't need to create triggers in Oracle to do this, as I have to with SQL Server 7. Again, I want consistency.

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.

>>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.
>
>Sorry, I missed this the first time (I am knew to SQL Server). Your premise is incorrect, at least so far as the cascades go. A developer or power user cannot change the surrogate key because of foreign key constraints.

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.

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

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

>Now, in my example, the use of surrogate keys explicitly prevents the changing of the PK. In your example, it is allowed, and the results are even worse. The SSN updates have cascaded, so all my data is consistent, but consistently bad. How are you going to catch this? You can't run a maintenance routine looking for orphaned records, as there aren't any. So how are you handling this in your current projects?

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

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

>A client may want to undelete a record because when we designed the system, they were use to the previous system, which bogged down when they had over 3 years of data. So, based on previous experience, they only want to keep 3 years around.

>I know better, so when the three year time limit is reached, I mark the records as inactive rather than delete them. Disk space is cheap. Then, when the new manager comes in and wants to look at trends, no one is in trouble, especially me.

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

>A backup? In SQL Server? When I restore a backup in SQL Server, I restore the entire database at the point in time of the backup, not an individual table.

Restore it to anohter database and append the old data to the new database !

>And as far a performance goes, SQL Server scales just fine. If I run into a case where I need to delete data, and I'm sure I will, I will have to worry about cascades. But I'm hoping SQL Server will do it all for me by then. < g >

AFAIK, SQL server 2000 does !

Walter,