General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>>How would you insure that the entries in a given field are unique OR blank? That is, multiple blank records are OK but if data is entered, it must be a unique value. Setting up a candidate index doesn't work due to the blank fields.
>>
>>SELECT cField, CNT(*) AS nCnt ;
>> FROM YourTable ;
>> WHERE NOT EMPTY(cField) ;
>> GROUP BY cField ;
>> HAVING nCnt > 1 ;
>> INTO CURSOR Dups
>>
>>This gives you a list of duplicate values.
>>
>>Tamar
>
>Thanks Tamar. This would find duplicates if there are any, but how would you prevent them in the first place? I'm thinking some kind of code in both update and insert triggers might be the way to go, but I'm not really sure.
In a multi-user system, you have to do the check at the time you insert the record. Any sooner and there's a chance that someone else will sneak in with a duplicate before you save.
As Naomi says, you can use a row rule or an Insert trigger.
Tamar
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only