Hi Randy,

As far as I know, none of the RDBMS allows to create an unique index with FOR clause. You have to code the logic in one of the following area.

1. Front-end
2. Middle Tier
3. Back end

If your application will be used with different backend and with different front-end, I would suggest to put the rules in middle tier instead of writing trigger.

Triggers are fired after the fact and I am quite sure that Oracle trigger works the same way SQL Server trigger works.

Hope that helps.

Bye
Jayesh


>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