>FYI, SSN is not a good choice for a candidate/primary key. There are duplicates.
>
>
>
>>Hilmar,
>>
>>Awsome... Thanks for the code/info!
>>
>>Renoir
>>
>>>At the database level, uniqueness is enforced with candidate indices. Use them by all means. However, additionally, you may want to intercept before VFP does, to give the user more meaningful error messages than "Trigger Failed" or "Key violation".
>>>
>>>For this purpose, use the following function before saving the record, and show the appropriate error message, like "SSN already exists":
>>>
>>>

>>>**********************************************************************
>>>FUNCTION DuplicateKey(tcTag, tcTable, txPkExpression)
>>>	* Check for duplicate, according to a certain index tag.
>>>
>>>	* Parameters:
>>>	* tcTag: name of index tag.
>>>	* tcTable (optional): name of table. Required if working with views.
>>>	* txPkExpression (optional): primary key expression. Required when working with views.
>>>	*   This allows to omit current record from checking of duplicate.
>>>	*   Can't be used to check uniqueness of primary key (but I use non-business-value keys,
>>>	*   automatically generated, anyway).
>>>
>>>	* Returns: true if another record exists with the same value(s)
>>>	* (according to the specified index tag).
>>>
>>>	* Additional comments:
>>>
>>>	* By intercepting for this duplicate value before Visual FoxPro does (through Primary or
>>>	* Candidate Indices), the user can messages that are clearer than "Trigger failed"
>>>	* (at least with Visual Extend, "Trigger failed" doesn't give more details).
>>>
>>>	* The function can also be used to check duplicates in FoxPro 2.x;
>>>	* just change "local" to "private".
>>>
>>>	* Of course, depending on the index, this function may check whether the combination of
>>>	* two or more fields is unique - however, only the index tag is passed as a parameter.
>>>
>>>	local lnSelect, lcDbf, lnRecno, txEvalExpr, llDuplicate, lcExpression, lcOrder, llWorkOnView
>>>	llWorkOnView = not empty(tcTable)
>>>	lnSelect = select()
>>>	lcDbf = iif(empty(tcTable), dbf(), tcTable)
>>>	lnRecno = recno()
>>>	lcOrder = order()
>>>	if not llWorkOnView
>>>		set order to (tcTag)
>>>		lcExpression = key()
>>>	else
>>>		select 0
>>>		use (lcDbf) order (tcTag) again
>>>		lcExpression = key()
>>>		use
>>>		select (lnSelect)
>>>	endif
>>>	txEvalExpr = eval(lcExpression)
>>>	select 0
>>>	use (lcDbf) order (tcTag) again
>>>	seek txEvalExpr
>>>	if found() and (llWorkOnView and eval(tag(GetPkNum())) = txPkExpression;
>>>			or not llWorkOnView and recno() = lnRecno)
>>>		skip
>>>	endif
>>>	llDuplicate = (eval(lcExpression) = txEvalExpr)
>>>	use
>>>	select (lnSelect)
>>>	set order to (lcOrder)
>>>	return llDuplicate
>>>ENDFUNC && DuplicateKey
>>>

Craig;

My personal preference is to assign important information like SSN to a candidate key (never a primary key) as I want to alert the user of a violation. Therefore as Hilmar pointed out, trap the error and give the user a good explanation of what the problem is. Either it is a duplicate assigned in this case by the social security department (I have seen this happen) or someone incorrectly entered someone’s SSN (a more common occurance).

In either case I will alert the user that this must be resolved – call a supervisor, etc. Otherwise the error will go undetected under normal circumstances.

Tom