Author: Ajoy Khaund
Posted: 2008-11-11 at 08:40:10
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: 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"
--------------------------------------------------
From: "Dave Crozier" <DaveC@Flexipol.co.uk>
Sent: Tuesday, November 11, 2008 1:27 PM
To: <profoxtech@leafe.com>
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: 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]