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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] Mail: [EMAIL PROTECTED] "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] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
