Ohhh man....

I don't understand why you don't get the point. It's not that difficult. Using surrogates do NOT have anything to do with this particular problem. You're shifting the problem from the PK to the CK.

I don't believe that a man like you can make such unthought statement and hold on to a stupid standpoint like this. It makes no sense !

I know you won't react to this one, but I'll state it for the others who will read this message. The following text i've written before and reffered to this this many times. You've never taken the effort to react to this message. Just saying you don't agree a very cheap statement IMO. Just saying we have been there before is a nice statement for you and me, but does not help Javed very much. If you really feel if I'm wrong in this statement then argue the point i'm making in the following text.


Lets say you've got a table where the column article is unique. You can implement this in two ways:
TABLE 1:
Articleno = Primary key

TABLE 2:
Pk = Primary key (integer surrogate keys)
Articleno = candidate key

The common mistake people make when saying that surrogate keys solve the problem is that they solve the problem in the following manner:

TABLE 3:
Pk = Primary key
Articleno = normal attribute

Well now you've got the same and other problems. Of course you've got to be sure that articleno is unique since it makes no sense to have two records of articleno "BIKE". Well how you're going to do this ? Some people will try to solve it by using SEEK() to check if a key already exist before saving. However this method is not safe. SEEK() fails in certain circumstances (e.g. When holding local READ Buffers and other users are modifying, Inserting or deleting records). Above all, it only checks for dublicates in your program, you can not prevent entering duplicate values from the command window (Except when using triggers). Neiter prevents the existance of duplicate keys, it only prevents adding them (which makes a lot of difference when there was a bug in you duplicate checking mechanism in the past)

The only reliable way the prevent dublicates is either by using Primary or candidate indexes.

CHOOSING TABLE 1

When having deleted() records you can face the problem that when you try to add a new article, it errors you because the same key already exists in a deleted() record. Therefore you've got to FILTER (FILTER clause in INDEX or ALTER TABLE PRIMARY KEY command) the index for deleted records by adding a FOR !DELETED() filterclause.
To enable rushmore to use the PK for optimization you've got to add a normal index with:
INDEX ON Articleno TAG Artic2

CHOOSING TABLE 2

The PK value is generated automaticly, so there is no need to filter the primary key. However to force uniqueness of the candidate key you've got to add the following index:
INDEX ON Articleno TAG Articleno CANDIDATE FOR NOT DELETED()
and a:
INDEX ON Articleno TAG Artic2
to enable rushmore to optmize queries where the Articleno is used.

When you look at the two solutions you'll come to the conclusion that in both cases the artcile column has two indexes attached: One normal, and one filtered for DELETED() primary or candidate key. Adding a surrogate PK key in table 2 does not change much.

Walter,