Creating Indexes

[Valcor]

Hi All,

This is really been racking my brain, and I cannot find a bit about it anywhere. We're working with a btrieve database system, but were looking into using Pervasive.SQL to allow people to create add-ins for our program. An issue arrived when trying to create indexes. Some of our indexes include NULL KEYS, and MANUAL KEYS. I cannot see how to set these up in a Pervasive.SQL 2000 statement. For example::

create table AR1
(
RECPOS Integer,
RUSER Char( 2 ),
ARACT Smallint,
ARFLY tinyint,
ARNUM Char( 12 ),
DUM1 Char( 20 ),
ARNAME Char( 50 ),
ARADD1 Char( 25 ),
ARADD2 Char( 25 ),
ARCITY Char( 20 ),
ARSTATE Char( 20 ),
ARZIP Char( 10 ),
ARCONTACT Char( 30 ),
ARFONE Char( 20 ),
ARFAX Char( 20 ),
)#
Create Unique INDEX XAR1idx0 ON AR1 ( ARNUM ) #
Create INDEX XAR1idx1 ON AR1( ARNAME ) #
Create INDEX XAR1idx2 ON AR1( ARZIP )#
Create INDEX XAR1idx3 ON AR1( ARCONTACT )#
Create INDEX XAR1idx4 ON AR1( ARFONE )#
Create INDEX XAR1idx5 ON AR1( ARMISC1 )#
Create INDEX XAR1idx6 ON AR1( ARACT, ARNUM )#
Create INDEX XAR1idx7 ON AR1( ARFLY, ARNUM )#


This is the statement I have, but I cannot work out where I'm supposed to put in MOD, NULL, or MAN, and the documentation is brief on the CREATE INDEX syntax. Any assistance would be appreciated.

-- Valcor

 

[Vancouverite]

MOD is the NOT MODIFIABLE modifier to the CREATE INDEX, e.g.

CREATE Unique Not Modifiable Index XAR1dx0 ON AR1 (ARNUM)

The other two (Null and Manual keys) are not supporyed directly by PSQL. To set these, you will need to create the table / definition, then use the maintenance utility to set the flags.

 

[Linda84]

To add on to Vancouverite's response - the Null and Manual key attributes can not be represented in the DDFs. Do NOT try to use the NULL feature of SQL, this is a completely different concept.

It sounds like you're trying to make dictionaries for existing Btrieve files. If so, just use the CREATE TABLE and CREATE INDEX with the IN DICTIONARY clause so that you don't affect the physical file. You have to have PSQL 2000i with SP4 to do this.

When you're done, run a Check Database to make sure they match, and any inconsistencies it reports relative to the Null/Manual Keys can be ignored.