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
---
something like
INDEX ON yourfield CANDIDATE FOR !EMPTY(yourfield)
?
> I have field which can be empty else it must be unique. Can I create such a index which will throw an error.
>
> Ajoy Khaund
>
>
--
Jean MAURICE
Grenoble - France - Europe
www.atoutfox.org
www.aedtf.org
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]
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]
On Tue, Nov 11, 2008 at 7:40 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
> 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.
----------------------------------
I would validate that # before I did the row insert. Is there a high
velocity on inserts to this table? If so create a trigger for insert
to gen it at insert.
--
Stephen Russell
Sr. Production Systems Programmer
Mimeo.com
Memphis TN
901.246-0159