>>Snip...
>>>
>>>I'm going to use our PK generator to generate a surrogate key in a customer
>>>table to try it out.
>>>But my primary problem is that I have 2 very large tables with PK:s that
>>>consists of 5 fields. It's updated daily with statistics from customer counters
>>>and from registers in 70 stores. And sometimes the updated data is corrupt.
>>>So when I delete a few hundered records and insert them again with updated
>>>data (the PK fields doesn't change) there's a uniqness violation.
>>>I could pack them but because of their size it takes a lot of time.
>>>The solution I'm looking for is a way to get around the uniqness problem, and
>>>I've tried with a filtered PK and it works. But when the tables grow what
>>>will happen with the filtered indexes will they become slow?
>>>
>>>/Magnus
>>Magnus,
>>To me a PK is not an identifier for a record. It's used as an insurance that when I get one I'll reach all of correct related child records. It's the ultimate linker key. Apart from finding related records not used for finding a particular record (it's a meaningless unique key). In this sense I would use the 5 field key as a locator with a regular index (filtered or not). But using them or generated keys, PK shouldn't change for they're locators of child records thus a uniqueness problem would arise with del & insert. I think instead of deleting then inserting, changing the needed values in target records is more effective. I never tried a filtered PK and don't have an idea how it would work. I wouldn't expect a filtered index become slow as tables grow. Still I would suggest instead of using filtered keys, do a replacement in the related records, removing necessity for del&insert (if via view SQL update instead of SQL del&insert).
>>Cetin
>
>Hi Cetin.
>Thank you for pointing me into the rigth direction. I will use the PK as
>a linker key and nothing else. But I think I have another problem. There is
>no problem in controlling uniqness within VFP programatically, but if someone connects to a VFP table via ODBC how can I controll that he doesn't insert
>a duplicate record? This I don't know.
>/Magnus
Hi Magnus,
Back from holiday.
Still for that I suggest not using a PK but a candidate key. Also you could insert code for preventing duplicates to stored procedures and use insert, update, delete triggers.
Cetin