>First of all, HAPPY NEW YEAR to you !!

And to you.

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

I do not know what you mean by FORCED. I have yet to come across a case where I would not use surrogate 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?

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

Yes, I understand.

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

As am I. In general, every system I have worked on has needed a surrogate key in at least one of the tables. If I apply surrogate keys to all the tables, I can treat them the tables in a consistent manner.

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

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

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

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.

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

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

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

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

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

This is relatively easy in VFP, but SQL Server is a different matter. I am not saying it can't be done in SQL Server, but not something I would look forward to.

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

I hope so.