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

Yes, I would add a surrogate key. The benefit to the current scheme is that I can treat all my tables in a consistent manner. The all have surrogate keys, each is named TableName + "ID". My argument is still the same: consistensy. I'll gladly trade a little overhead, and surrogate keys are very little overhead, for consistency.

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

Simple answer: don't use a framework that makes you do this. I don't.

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

By adding a surrogate key to one table in my database that does not require it? How is this driving consistency too far?

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

There may be, but I think you are missing my point. SQL Server 7 does not handle cascades natively. Oracle apparently does. You have to write triggers to handle cascades in SQL Server 7. You do not in Oracle. I want consistency across my backends. This is one case where I do not get that. With surrogate keys, I do. Simple as that.

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

See above.

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

Yes, and my point still stands. In your original example, a developer or power user changed the surrogate key in my system. I am saying that with foreign key constraints, which I use, SQL Server will not allow this. So the problem you presented cannot happen in my system.

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

I feel like I am increasingly repeating myself. DBMS are different, but I want as much consistency as possible. With surrogate keys, I am one step closer to getting that. With intelligent keys, I have to handle the update cascades differently in the DBMS. I believe that Oracle handles the cascade natively, while with SQL Server 7, I have to write triggers.

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

That is exactly my point. You started your argument with a developer or power user changing a surrogate key. I have pointed out where SQL Server does not allow this. Now, our systems are the same in that if someone changes the SSN incorrectly, the data is still bad. It is consistent, but in both scenarios, it is still bad. Your argument is not holding up.

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

Again, this is exactly my point. Your argument about a power user changing a surrogate key has the exact same effect in both systems: bad data. So where is your advantage?

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

I think this is going to be a little more difficult than you think. In SQL Server 7, you can only have one server on a machine. So, to setup a second server, you have to use a different system. And what about liscensing requirements? Are you sure you have a computer around that you can install SQL Server 7.0 on? Does it have enough memory, etc.?

Now, you may be able to restore the database to a different database on the same server, but you still run into trouble. You can probably use DTS to copy the old data to the new data, but you have to be careful.

One, you probably do not want to copy old data over new data, right? So, you have to be selective in what you restore. How are you going to do this?

Also, I could not just append records from the the Invoice table. What if the Customer record for an Invoice had also been deleted? You would have to restore the Customer record with the Invoice record. What if when restoring the Customer table, I have a duplicate CustomerNo? This is another example of where your intelligent keys argument has problems.

Let's say that 10 years ago, I had a customer named Acme Technologies with a CustomerNo of "ACME001". We did business with them on and off for a few years, then they went of business. 3 years ago, I backed up my data, then started deleting old customers. ACME001 was deleted.

Now, just a year ago, we picked up a new customer, Acme Industries. We gave them a CustomerNo of "ACME001". How do you avoid this scenario, and if it happens, what are you going to do?