Dave Thanks and also to Maurice. This field is not a very important field. In a employee table permanent workers have a Provident Fund Number which the temporary guys don't have. Now I have to have check when saving the record to confirm the number does not exist.
Where would you put a check normally. 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" -------------------------------------------------- From: "Dave Crozier" <[EMAIL PROTECTED]> Sent: Tuesday, November 11, 2008 1:27 PM To: <[EMAIL PROTECTED]> Subject: RE: index column Unique or empty > 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.
