Ajoy,

Your requirement breaks all the standard Database rules. Either the index

should be unique or not. The constraint of being unique is there for a

reason - else why have it.

You are better off leaving the unique field as it is and always filling it

in, then adding a sub field, for example lInclude (Boolean).

Field Name Type Length

Unique_Key C 5

lInclude Logical 1

Next create a new composite index based upon the two fields using long hand

or a UDF which will convert the Logical field to a character expression:

Index on iif(lInclude, "1","0")+Unique_Key to tag Live

Note that personal preference may dictate that you swap the order of the

index so that we postfix the unique key as to prefixing it.

Then all you have to do is always prefix/postfix your key with "1" if you

need to find a valid unique key before you do the search for normal

live/included record searches.

Personally I prefer the Prefix option as it easily allows you to find all

"non included" records.

Also you can switch a record's Included/Excluded status very easily without

having to change the main key. Obviously if you do it this way then either

the Unique_Key could become the Primary Key by definition or you could use

the existing primary Key if you have one.

Jean Maurice's idea about using a filtered index is OK but you won't be able

to use Rushmore to optimize your index access on a filtered index.

Remember that database rules regarding uniqueness are there for a reason,

but only when the database has been properly designed.

Dave Crozier

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf

Of Ajoy Khaund

Sent: 11 November 2008 06:44

To: Profox

Subject: index column Unique or empty

Hi

I have field which can be empty else it must be unique. Can I create such a

index which will throw an error.

TIA

Ajoy Khaund

Neamati Road

Near Bhogdoi Bridge

Jorhat 785001-21

Assam, India

Tel: 91-376-2351288

Cell: 91-94350-92287

Mail: akhaund@hotmail.com

Mail: akhaund@gmail.com

"Walking on water and developing software from a specification are easy if

both are frozen."

- Edward V. Berard, "Life-Cycle Approaches"

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

[excessive quoting removed by server]