Randy,

How about making the column accept nulls and put a unique constraint on it. This will get you what you want without writing any code. (Except the code to handle the error.

BOb


>I have a couple of situations where a field can be empty, but if not empty, cannot be a duplicate. One example is a table of people, where only some of the people are users. The person record has an optional User ID column that, if non-empty, must be unique.
>
>With local VFP data, there are tricks with FOR clauses on indexes that can be used to enforce this. But I want a good solution for n-tier development and a SQL back-end. My assumption is that I will need to use triggers for this (insert/update) and that the code would, therefore be back-end specific. Does that sound right, or do SQL Server and Oracle support a more declarative approach to avoiding this type of duplicate value?
>
>Assuming I do have to write the trigger, can anyone with Oracle experience tell me whether the current record's value is considered part of the table at the time the trigger is executed? IOW, if I tried to change a User ID value to "johndoe", would I need to check for *any* matching record or 2+ matching records. (With VFP data, it is the latter.)
>
>TIA