Adding index tags to columns 2

[audiopro]

I need to add some columns to a client's existing free table. Adding the columns is straight forward enough ....

alter table z:/data/customer add column invites L

but how do I add indexes to these columns?

The manual gives this example

ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2

but What are the values of:-
- eExpression3
- lExpression4
- TagName2


Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Hi Keith,

You need something like this:

SELECT Customer
INDEX ON Invites TAG Invites

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

Thanks Mike, I have always used table designer before to set the index. I thought 'Index On' commands were runtime only, I hadn't realised that the index is permanently changed. I thought that the table would have to be altered, that is so much easier.
Thanks very much

Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Glad to help, Keith. Actually, I think a lot people are under the impression that the indexes created with INDEX ON are temporary and have to be recreated each time, which isn't the case.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

One of the changes I need to make is to add a seldom used index tag, is it wise to remove unused tags for speed or does it not make a lot of difference.
How are they removed?

The table has about 3000 records and is growing slowly.


Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Keith,

You use DELETE TAG to delete an index (which is why you need to specify a tag name in the INDEX ON command).

Deleting unnecessary tags will make it faster to update the table (including deleting and inserting records). But it will slow down any queries in which the condition involves the indexed field(s). If you rarely do any such queries, then you should remove the tag.

That said, you won't notice a huge difference either way if table only has a few thousand records.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

Thanks Mike - I think I will leave them in to save me time later.
I find VFP good in many ways but using the wizards make a programmer forget those essential, basic commands. I grew up in the DOS age and suspect you did too. It is the same with HTML, although I still write that code manually.


Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Keith,

I grew up in the DOS age and suspect you did too.

I did. Actually, I grew up in the COBOL age, but let's not talk about that.

It is the same with HTML, although I still write that code manually.

I do too. I originally did my main site in FrontPage. I rewrote it by hand mainly to get fluent in HTML and CSS. It was only then that I realised what terrible code FrontPage generates.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SnyAc]

Yes... let's leave references to the COBOL age in the past... lol

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[Olaf Doschke]

the only downside is INDEX ON does not create a primary index.

To answer the inital question:
-eExpression3 would be the index expression, that is normally the ID field of the table
-[FOR lExpression4] means it's optional (and in fact wouldn't make sense for primary indexes) - would create a filtered index only for the rows matching this condition. Eg you could create an index FOR NOT DELETED(), to only put undeleted records in the index.
-TagName2 - Well, the tag, thae name of the index.

All three are the parameters you can also pass to the INDEX command: INDEX eIndexExpression TAG cTagName FOR lCondition

You may INDEX ... CANDIDATE, which in fact would create a secondary key.

I think you wouldn't want to use the ADD PRIMARY KEY clause to a boolean field. This would only allow 2 records, one with .T. , one with .F., cause no .NULL. is allowed and no double values.

For a boolean field you may want to add the BINARY option to the INDEX ON command, to create an even compacter binary index, which is only valid for boolean index espressions.

Bye, Olaf.

 

[audiopro]

Thanks for the explanation.

In this case I was changing an existing table. It would be unusual for me to add a new table to an existing app.

Keith

http://www.studiosoft.co.uk

 

[Olaf Doschke]

Excuse me audiopro,

where was I or someone else talking about adding a new table?

Bye, Olaf.

 

[audiopro]

Sorry if my reply was unclear.
I did not fully understand the terminology. Having only ever used the table designer to create tables I was unaware of the type of index I required. I now realise that I needed to add regular indexes and not primary indexes as I was attempting.


Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Keith,

In addition, I think you mentioned that this is a free tables. As you probably know, VFP doesn't support primary keys for free tables. Just make it a normal index and you'll be fine.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk