ALTER TABLE problem! 
Author Message
 ALTER TABLE problem!

To anyone:

The ALTER TABLE command seems to have a problem with FOR expressions

The help documentation says that the format for adding a primary key is this:
ALTER TABLE TableName1
     ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]

There is an example of this command also in the help:
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

But (!!!!!), the following does NOT work...
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR .NOT. DELETED()

To eliminate the possibility of just functions dying in the for expression, I also tried the following (contrived) example, which does not work, either:
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

Regardless of the arguments for or against the use of the !deleted() clause or any filtered clause in the primary key (because 1. Frankly, I don't want to get into that discussion, 2. It is not relevant to the problem at hand, and 3. That whole argument is suitable for a thread of its own), the code does not work as advertised (please do not waste the time to tell me how awful filtered primary keys are).

Is there a trick to it or is this a bug? ! need to be able to dynamically recreate primary keys from existing data. This data has a "for .not.deleted()" for expression on the primary key for every table. This is not something I can change (nor would I even if it were possible). If there is an alternative to ALTER TABLE for programmatically setting primary keys that works with filtered indexes, I could live with that.

Any help would be appreciated.

Tks in advance,
-Tom

--



Thu, 18 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!
Thomas,

AFAIK it's a known bug.

Quote:
> ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

does not work.
If you drop the TAG clause it should work:
ALTER TABLE customer ADD PRIMARY KEY cust_id FOR (cust_id>0)

-Stefan


To anyone:

The ALTER TABLE command seems to have a problem with FOR expressions

The help documentation says that the format for adding a primary key is this:
ALTER TABLE TableName1
     ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]

There is an example of this command also in the help:
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

But (!!!!!), the following does NOT work...
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR .NOT. DELETED()

To eliminate the possibility of just functions dying in the for expression, I
also tried the following (contrived) example, which does not work, either:
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

Regardless of the arguments for or against the use of the !deleted() clause
or any filtered clause in the primary key (because 1. Frankly, I don't want
to get into that discussion, 2. It is not relevant to the problem at hand,
and 3. That whole argument is suitable for a thread of its own), the code
does not work as advertised (please do not waste the time to tell me how
awful filtered primary keys are).

Is there a trick to it or is this a bug? ! need to be able to dynamically
recreate primary keys from existing data. This data has a "for
.not.deleted()" for expression on the primary key for every table. This is
not something I can change (nor would I even if it were possible). If there
is an alternative to ALTER TABLE for programmatically setting primary keys
that works with filtered indexes, I could live with that.

Any help would be appreciated.

Tks in advance,
-Tom



Thu, 18 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!
Stefan,

Dropping the "TAG" in the ALTER TABLE ADD PRIMARY KEY ... worked perfectly.
Thank you VERY much!

-Tom


Quote:
> Thomas,

> AFAIK it's a known bug.
> > ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)
> does not work.
> If you drop the TAG clause it should work:
> ALTER TABLE customer ADD PRIMARY KEY cust_id FOR (cust_id>0)

> -Stefan



Fri, 19 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Thomas,

The FOR filter for Primary indexes is NOT supported through the ALTER TABLE command, only in the visual designer.

--
JimB
www.jamesbooth.com


  To anyone:

  The ALTER TABLE command seems to have a problem with FOR expressions

  The help documentation says that the format for adding a primary key is this:
  ALTER TABLE TableName1
       ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]

  There is an example of this command also in the help:
  ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

  But (!!!!!), the following does NOT work...
  ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR .NOT. DELETED()

  To eliminate the possibility of just functions dying in the for expression, I also tried the following (contrived) example, which does not work, either:
  ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

  Regardless of the arguments for or against the use of the !deleted() clause or any filtered clause in the primary key (because 1. Frankly, I don't want to get into that discussion, 2. It is not relevant to the problem at hand, and 3. That whole argument is suitable for a thread of its own), the code does not work as advertised (please do not waste the time to tell me how awful filtered primary keys are).

  Is there a trick to it or is this a bug? ! need to be able to dynamically recreate primary keys from existing data. This data has a "for .not.deleted()" for expression on the primary key for every table. This is not something I can change (nor would I even if it were possible). If there is an alternative to ALTER TABLE for programmatically setting primary keys that works with filtered indexes, I could live with that.

  Any help would be appreciated.

  Tks in advance,
  -Tom

  --



Mon, 29 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Jim,

You're wrong on this one. The ALTER TABLE ADD PRIMARY KEY  command does support filtered indexes. However there are MAJOR (!!!!!) bugs with this command (perhaps these will be fixed in the final release of v7?):

1. The TAG keyword must be omitted if there is a FOR expression, so you cannot specify the tag name.
2. If there is a FOR expression, the fieldname cannot be longer than 10 characters.

If there is no FOR expression, the command works properly. Have not tested this with candidate keys, but I suspect the results are the same.

The need for filtered indexes is almost entirely rooted in the fact that deleted() records cause errors for primary and candidate keys if there are duplicate deleted records. Personally, I ONLY care if I have unique live records, deleted() records are not something that I need or want to worry about. I am definitely not alone in this.

If someone were to do a survey, I suspect that they would find that at least half (probably more) of all the VFP applications that use local tables and primary/candidate keys in the field make use of filter expressions in their primary/candidate keys. Of those that use filter expressions, I would guess that 99.999%+ of those expressions are "FOR not deleted()".

While the ability to work with/reuse deleted records is pretty handy (it would be nice if the INSERT command could reuse deleted records, or it supported an INSERT FROM NAME (to make use of the SCATTER NAME objects)), I do not need those records included in either the primary key or any candidate keys.

In most other databases, such as MS-SQL, deleted records are ignored whether they physically still exist in the table or not. With SET DELETED ON deleted records are mostly ignored in VFP, but not entirely. If DELETED is ON, you cannot SEEK a deleted value using the indexes, but if the index is primary/candidate w/o a FOR !deleted() filter, an insert fails if you try to add the value. This is a major PITA. Hence the common use of the FOR !deleted() expression.

I would like for candidate/primary indexes to ignore deleted() records by default, and allow some sort of non-default flag if you want to include deleted() records. If this cannot happen, then, dammit, the commands that reference the FOR expressions (such as ALTER TABLE) in these indexes MUST be fixed.

As a side note, the help docs say that the ALTER TABLE command works with primary/candidate keys and FOR expressions. Is there anyone at MS that checks to make sure that VFP works as documented? If there is, they need to do a better job.

-Tom

  Thomas,

  The FOR filter for Primary indexes is NOT supported through the ALTER TABLE command, only in the visual designer.



Mon, 29 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Quote:

>If someone were to do a survey, I suspect that they would find that at
least half
> (probably more) of all the VFP applications that use local tables
>and primary/candidate keys in the field make use of filter expressions in
>their primary/candidate keys. Of those that use filter expressions, I
>would guess that 99.999%+ of those expressions are "FOR not deleted()".

Argh !!! never use "FOR not deleted()" in an index ! This prevent any
rushmore optimization... I had once to maintain a FPD (Foxpro DOS 2.6 for
those who remember) application which had all its indexes built on "FOR !
DELETED()". The funny thing is that the guy who wrote this application told
me that SELECT was almost every time much slower than SEEK + SCAN WHILE...
Of course ! Using "FOR NOT DELETED()" disable any rushmore optimization:
Rushmore can't make use of these indexes.
If you really need this filter, use SET DELETED ON and perhaps an INDEX ON
DELETED() TAG DELETED on the table.

Quote:
>In most other databases, such as MS-SQL, deleted records are ignored
whether
>they physically still exist in the table or not. With SET
>DELETED ON deleted records are mostly ignored in VFP, but not entirely.
>If DELETED is ON, you cannot SEEK a deleted value using the
>indexes

you just have to issue a SET DELETE OFF before the seek, I don't see the
point...

Quote:
>but if the index is primary/candidate w/o a FOR !deleted() filter, an
insert fails
>if you try to add the value. This is a major PITA. Hence
>the common use of the FOR !deleted() expression.

I don't understand why you want to use a value already used by a deleted
record. If you really need this, replace the primary value of the deleted
record with a negative one. This way you could easily group your deleted
records... Just an idea...

Quote:
>I would like for candidate/primary indexes to ignore deleted() records by
default,
>and allow some sort of non-default flag if you want to include
>deleted() records. If this cannot happen, then, dammit, the commands
>that reference the FOR expressions (such as ALTER TABLE) in these
>indexes MUST be fixed.

You are right, it's must be fixed. Though, if you plan to use this kind of
index, give up the idea of using SELECT statements with more than one
table...

Quote:
>As a side note, the help docs say that the ALTER TABLE
>command works with primary/candidate
>keys and FOR expressions. Is there anyone at MS that checks
>to make sure that VFP works as
>documented? If there is, they need to do a better job.

Let's hope that in a Service Pack 4 your problem will be solved ...

- Manu

(You shouldn't use HTML to format your post, not every newsreader is able to
make use of that)



Tue, 30 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Hi Jim,
Sure it is supported, it's just documented wrong.
I just tried this, No problem;
use dummy
alter table dummy drop primary key
alter table dummy add primary key dummy for not deleted('dummy') tag dummy
-Anders


  Thomas,

  The FOR filter for Primary indexes is NOT supported through the ALTER TABLE command, only in the visual designer.

  --
  JimB
  www.jamesbooth.com


    To anyone:

    The ALTER TABLE command seems to have a problem with FOR expressions

    The help documentation says that the format for adding a primary key is this:
    ALTER TABLE TableName1
         ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]

    There is an example of this command also in the help:
    ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

    But (!!!!!), the following does NOT work...
    ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR .NOT. DELETED()

    To eliminate the possibility of just functions dying in the for expression, I also tried the following (contrived) example, which does not work, either:
    ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

    Regardless of the arguments for or against the use of the !deleted() clause or any filtered clause in the primary key (because 1. Frankly, I don't want to get into that discussion, 2. It is not relevant to the problem at hand, and 3. That whole argument is suitable for a thread of its own), the code does not work as advertised (please do not waste the time to tell me how awful filtered primary keys are).

    Is there a trick to it or is this a bug? ! need to be able to dynamically recreate primary keys from existing data. This data has a "for .not.deleted()" for expression on the primary key for every table. This is not something I can change (nor would I even if it were possible). If there is an alternative to ALTER TABLE for programmatically setting primary keys that works with filtered indexes, I could live with that.

    Any help would be appreciated.

    Tks in advance,
    -Tom

    --



Tue, 30 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Thomas
You can specify a TAG as long as the TAG clause is placed after the FOR expression. This also allows you to have as long a column name as you like; just make the tag name ten characters, which is the maxmum length allowed for a tag name.
-Anders


  Jim,

  1. The TAG keyword must be omitted if there is a FOR expression, so you cannot specify the tag name.
  2. If there is a FOR expression, the fieldname cannot be longer than 10 characters.



Tue, 30 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Anders,

I thought I remembered that getting introduced at some point.

--
JimB
www.jamesbooth.com


  Hi Jim,
  Sure it is supported, it's just documented wrong.
  I just tried this, No problem;
  use dummy
  alter table dummy drop primary key
  alter table dummy add primary key dummy for not deleted('dummy') tag dummy
  -Anders


    Thomas,

    The FOR filter for Primary indexes is NOT supported through the ALTER TABLE command, only in the visual designer.

    --
    JimB
    www.jamesbooth.com


      To anyone:

      The ALTER TABLE command seems to have a problem with FOR expressions

      The help documentation says that the format for adding a primary key is this:
      ALTER TABLE TableName1
           ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]

      There is an example of this command also in the help:
      ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

      But (!!!!!), the following does NOT work...
      ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR .NOT. DELETED()

      To eliminate the possibility of just functions dying in the for expression, I also tried the following (contrived) example, which does not work, either:
      ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id FOR (cust_id>0)

      Regardless of the arguments for or against the use of the !deleted() clause or any filtered clause in the primary key (because 1. Frankly, I don't want to get into that discussion, 2. It is not relevant to the problem at hand, and 3. That whole argument is suitable for a thread of its own), the code does not work as advertised (please do not waste the time to tell me how awful filtered primary keys are).

      Is there a trick to it or is this a bug? ! need to be able to dynamically recreate primary keys from existing data. This data has a "for .not.deleted()" for expression on the primary key for every table. This is not something I can change (nor would I even if it were possible). If there is an alternative to ALTER TABLE for programmatically setting primary keys that works with filtered indexes, I could live with that.

      Any help would be appreciated.

      Tks in advance,
      -Tom

      --



Tue, 30 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!

Anders, you're a genius. Works like a charm. Thank you a bunch!
-Tom

  Thomas
  You can specify a TAG as long as the TAG clause is placed after the FOR expression. This also allows you to have as long a column name as you like; just make the tag name ten characters, which is the maxmum length allowed for a tag name.
  -Anders



Wed, 31 Jul 2002 03:00:00 GMT  
 ALTER TABLE problem!
Manu,

Careful about saying "Never".

But you did make some good points. You are probably in the half (or less)
that doesn't use filtered indexes. That's valid. There are also lots of
applications (as a consultant I see a lot of systems) that don't even use
primary/candidate keys (!).

The reasoning for and against using filtered indexes is a wash. For every
advantage there is a disadvantage, and vice-versa. There are some basic
philosophical differences all over the place in the development world that
must be respected. Here are some examples:

1. Codebook vs. Non-codebook standards. The issue here is not necessarily
what standard is followed, but that a standard exists and is followed at
all. For example, in my company, we do a lot of development (some of it
cross-development) in both VFP and Delphi, so we have implemented compatible
standards for both Delphi and VFP (similar but still different because of
the unique characters of both languages) that allows for intelligent
VFP/Delphi discussions and communications.

2. Reuse of primary and candidate keys. There are camps that say NEVER do
that and jump and down, yet it happens all the time. My company is somewhere
in the middle on this. The main distinction with our approach is that we
consider deleted() records as dead (versus "live") records. For security
reasons, we routinely blank out the deleted records as part of the deletion
process. So, in a way, if you count deleted records (we don't) we are
reusing primary keys (all blank deleted records have the same key values).
Other reasons for it include cross-ref tables (many-many) where the primary
key is the two keys that are being joined.

3. Never use compound keys. Another one. It's nice when it makes sense, but
there are plenty of times when it doesn't.

4. Filtered indexes. Yes, they kill Rushmore optimizations for that index,
but so what? But if they make sense (and in the context of my company's
applications, and in a lot of existing applications, they do), then go for
it. If Rushmore is needed for those indexes, another can be created w/o the
for expression. This situation generally (in our case anyway) arises only
for primary and candidate keys and almost exclusively the filter is FOR
!deleted().

5. Code in object methods. There are some camps (like the anti-snippet camps
of the past) that advocate very little code in object methods, instead use
calls to form methods. Actually is generally good practice. But not always
the optimal approach.

6. Anything else. Been doing this doo-doo for so long and have seen so many
ways that developers accomplish things, that I have become a lot less
judgmental over the years. There are plenty of things that I would have
jumped up and down about in the past, but now I look deeper. In many cases,
I have seen talented developers use what I would consider "bad" techniques
to very good effect (to my surprise!), and in some cases even do some things
that I would have sworn you couldn't do.

In summary, when you close your head to alternative approaches that don't
agree with your own pre-conceptions on how things should be, you wind up
missing a lot. My general opinion is that if an approach is well thought
out, and it works, I can roll with it, and often learn something new (tough,
since I already know everything, but it happens).

I do appreciate the healthy exchange of opinions, however. Discussions on
the relative merits of competing methods of accomplishing goals are
something that everyone can learn from. If you would like to debate the
filtered vs. non-filtered index approach, I would be happy to discuss it
further, but it deserves it's own thread as it will spawn some other issues.
If you want to start a new thread, I'll respond. Toss the gauntlet. This is
fun!

-Tom

PS. I know about the HTML. Sorry. It's just that some posts beg to be
properly formatted. For example, I am appalled at how my non-html messages
look once they are posted because of the way the linefeeds are added.


Quote:

> >If someone were to do a survey, I suspect that they would find that at
> least half
> > (probably more) of all the VFP applications that use local tables
> >and primary/candidate keys in the field make use of filter expressions in
> >their primary/candidate keys. Of those that use filter expressions, I
> >would guess that 99.999%+ of those expressions are "FOR not deleted()".

> Argh !!! never use "FOR not deleted()" in an index ! This prevent any
> rushmore optimization... I had once to maintain a FPD (Foxpro DOS 2.6 for
> those who remember) application which had all its indexes built on "FOR !
> DELETED()". The funny thing is that the guy who wrote this application
told
> me that SELECT was almost every time much slower than SEEK + SCAN WHILE...
> Of course ! Using "FOR NOT DELETED()" disable any rushmore optimization:
> Rushmore can't make use of these indexes.
> If you really need this filter, use SET DELETED ON and perhaps an INDEX ON
> DELETED() TAG DELETED on the table.

> >In most other databases, such as MS-SQL, deleted records are ignored
> whether
> >they physically still exist in the table or not. With SET
> >DELETED ON deleted records are mostly ignored in VFP, but not entirely.
> >If DELETED is ON, you cannot SEEK a deleted value using the
> >indexes

> you just have to issue a SET DELETE OFF before the seek, I don't see the
> point...

> >but if the index is primary/candidate w/o a FOR !deleted() filter, an
> insert fails
> >if you try to add the value. This is a major PITA. Hence
> >the common use of the FOR !deleted() expression.

> I don't understand why you want to use a value already used by a deleted
> record. If you really need this, replace the primary value of the deleted
> record with a negative one. This way you could easily group your deleted
> records... Just an idea...

> >I would like for candidate/primary indexes to ignore deleted() records by
> default,
> >and allow some sort of non-default flag if you want to include
> >deleted() records. If this cannot happen, then, dammit, the commands
> >that reference the FOR expressions (such as ALTER TABLE) in these
> >indexes MUST be fixed.

> You are right, it's must be fixed. Though, if you plan to use this kind of
> index, give up the idea of using SELECT statements with more than one
> table...

> >As a side note, the help docs say that the ALTER TABLE
> >command works with primary/candidate
> >keys and FOR expressions. Is there anyone at MS that checks
> >to make sure that VFP works as
> >documented? If there is, they need to do a better job.

> Let's hope that in a Service Pack 4 your problem will be solved ...

> - Manu

> (You shouldn't use HTML to format your post, not every newsreader is able
to
> make use of that)



Wed, 31 Jul 2002 03:00:00 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Alter Table Problems

2. Alter Table Problem

3. Alter Table - Alter Column - Set Default ?'s

4. Programmatically ALTERing TABLE structure of SQL table from VFP

5. ALTER TABLE.....

6. Status Bar on Alter Table

7. Progress bar on Alter table

8. Altering Tables

9. alter table, vb and foxpro

10. vfp8 : alter table bug

11. vfp8 bug report (alter table command)

12. vfp8 alter table bug

 

 
Powered by phpBB® Forum Software