VA FileMan V. 22 Key and Index Tutorial
[<-- Previous Lesson]   Lesson 9 Quiz Button   [Next Lesson -->]

Part 3 - Keys

Version 22 of VA FileMan allows you to define a database key on a file. A database key is a set of one or more fields that, when taken together, uniquely identifies a record in a file.

VA FileMan stores key definitions in the KEY file (#.31), under the global root ^DD("KEY").


Lesson 9. Create a Key

This lesson covers the following topics:

1.  Key Integrity
2.  Uniqueness Index
3.  Primary and Secondary Keys

In the exercise that follows, you will create a key on the ZZINDIVIDUAL file.


Key Integrity

When you define a key, VA FileMan automatically enforces the integrity of that key. Key integrity means that:

1.  The key is unique for all records in a file.
2.  A field that is part of a key must have a value (i.e., it cannot be null).

Uniqueness Index

When you create a key in VA FileMan, an index, called the Uniqueness Index, is automatically created. This index contains as subscripts the fields in that key. The Uniqueness Index is simply a New-style Regular index that supports a key. VA FileMan uses the Uniqueness Index of a key to enforce the integrity of that key and to look up entries in the file based on the fields in the key.

For example, if you define a key and select NAME and SSN as the fields in that key, FileMan automatically creates a corresponding Uniqueness Index that contains as subscripts the NAME and SSN fields.


Primary and Secondary Keys

If a file has a key, exactly one key in that file must be designated the primary key. All other keys, if any, are secondary keys. VA FileMan enforces key integrity equally for both primary and secondary keys, but it uses the primary key as the principal means for looking up entries in a file. For example, in a ^DIC lookup, if the user enters a question mark (?) at the Select prompt, VA FileMan automatically displays the data in the primary key fields for each record listed.

Note: The .01 field should be defined as part of the primary key.


Exercise 9.1.  Create Your First Key

In this exercise you will define your first key on the ZZINDIVIDUAL file. The fields in that key will be the NAME field (#.01) and the SSN field (#.02).


Step 1. Use VA FileMan's Key Definition option to create a new key named "A":
Select OPTION: UTILITY FUNCTIONS
Select UTILITY OPTION: KEY DEFINITION

MODIFY WHAT FILE: ZZINDIVIDUAL// <Enter>
Select Subfile: <Enter>

There are no Keys defined on file #662nnn.
Want to create a new Key for this file? No// YES

Enter a Name for the new Key: A// <Enter> A
  Creating new Key 'A' ...
Note: Key names must be one uppercase letter. You should give the primary key of your file or subfile the name "A". Subsequent secondary keys should be given the names "B", "C", and so on.

Step 2. You are now presented with a one-page ScreenMan form. Here you can select the fields in your new key.

In the first row of the "KEY FIELDS" section, under the "Field" column, enter NAME (or .01). In the "Seq No." column, enter 1. In the second row of the "KEY FIELDS" section, enter SSN (or .02), and in the "Seq No." column, enter 2.

The screen should now look like this:
Number: nn EDIT A KEY Page 1 of 1

  File: 662nnn         Name: A             Priority: PRIMARY
  KEY FIELDS:
  ==========
  Field          Seq No.  File            Field Name
  -----          -------  ----            ----------
  .01              1      662nnn          NAME
  .02              2      662nnn          SSN
  Uniqueness Index:

   Index Details...

COMMAND:                    Press <>PF1>H for help    Insert


Note: The sequence number corresponds to the subscript number of the cross-reference value as it will be stored in the Uniqueness Index. The first field of every key should be given a sequence number of 1, the second 2, and so on.

Step 3. Press <PF1>E to exit the form.


Step 4. FileMan then indicates that it will create a Uniqueness Index to support the key you just created and prompts you for an index name. Select the default name "E".
I'm going to create a new Uniqueness Index to support Key 'A'
of File #662nnn.

Index Name: E// <Enter>

  One moment please ...
Step 5. If asked whether you want to build the new index, press Enter to select the default YES and press Enter again to continue.
Do you want to build the index now? YES// <Enter>
  Building new index ...  DONE!

Press RETURN to continue: <Enter>
Note: At this point a Uniqueness Index to support Key A has been created. It is a compound index named "E", and its subscripts (cross-reference values) are the NAME and SSN fields.

Step 6. Answer YES to check key integrity now.
Do want to check the integrity of this key now? YES

Checking key integrity ...
Step 7. If any of the records in your file violate key integrity, you are presented with a list of options. A problem will probably be found. If so, select option 3 to ignore the problem for now. We know that we want to define the NAME and SSN fields as Key A. If any of the existing data in the file causes key integrity to be violated, we will correct it later.
ERROR: The key is not unique and/or some records have key
field values missing.

     Select one of the following:

          1        Delete the Key (also selected on up-arrow)
          2        Re-Edit the Key
          3        Ignore problem (Be sure to fix later)

Enter response: 3 <Enter>  Ignore problem (Be sure to fix later)
Note: The problem here is that one or more records in the file violate the integrity of the key. There may be fields with missing key values, or two records may have exactly the same NAME and SSN. You will find out what the specific problem is in the next few steps.

Step 8. At this point, the key is successfully defined, and FileMan shows you the basic information about the key. At the list of options, choose VERIFY to determine which record(s) violated key integrity in Step 7.
Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: E
         Field(s):  1) NAME (#.01)
                    2) SSN (#.02)
Choose V (Verify)/E (Edit)/D (Delete)/C (Create): VERIFY
Step 9. Select Key A, the key you just created, as the key to verify. Since our test file has only a few records in it, don't store the internal entry numbers of the records that violate key integrity in a template - just press Enter at the "STORE THESE ENTRY ID'S IN TEMPLATE:" prompt. Press Enter at the "DEVICE:" prompt to print the results to the screen.
Which Key do you wish to verify? A// <Enter>
STORE THESE ENTRY ID'S IN TEMPLATE: <Enter>
DEVICE: HOME// <Enter>

KEY INTEGRITY CHECK SEP 21, 2000 14:35    PAGE 1

             Key: A (#nn), File #662nnn
Uniqueness Index: E (#nnn)
ENTRY #   NAME                    ERROR
-------   ----                    -----
14        MODIFIED,ENTRY          Missing Key Fields(s):
                                   SSN [662nnn,.02]


Note: You created this entry in a previous lesson. However, you never added an SSN for this record, and it is the only record that violates key integrity.

Step 10. Use VA FileMan's Enter or Edit File Entries option to correct edit the SSN field for the MODIFIED,ENTRY record to 123456789.
Select OPTION NAME: ENTER OR EDIT FILE ENTRIES

INPUT TO WHAT FILE: ZZINDIVIDUAL// <Enter>
EDIT WHICH FIELD: ALL// SSN
THEN EDIT FIELD: <Enter>

Select ZZINDIVIDUAL NAME: MODIFIED,ENTRY
SSN: 123456789
Step 11. Now, go back to the Key Definition option and check key integrity again.
Select OPTION NAME: UTILITY FUNCTIONS
Select UTILITY OPTION NAME: KEY DEFINITION

MODIFY WHAT FILE: ZZINDIVIDUAL// <Enter>
Select Subfile: <Enter>

Keys defined on file #662nnn:

  A  PRIMARY KEY    Uniqueness Index: E
         Field(s):  1) NAME (#.01)
                    2) SSN (#.02)

Choose V (Verify)/E (Edit)/D (Delete)/C (Create): VERIFY

Which Key do you wish to verify? A// <Enter>
STORE THESE ENTRY ID'S IN TEMPLATE: <Enter>

DEVICE: HOME// <Enter>  SYSTEM
KEY INTEGRITY CHECK SEP 22, 2000 10:00    PAGE 1

             Key: A (#nn), File #662nnn
Uniqueness Index: E (#nnn)
** NO PROBLEMS **


Note: This time, no problems were found! Every entry in the file has values for NAME and SSN, and the combination of those two fields is unique for all entries in the file.

End of Exercise 9.1.


Congratulations! You have just created your first key!



Lesson 9 Quiz Button Select this link to test yourself on what you've learned in this lesson.

[<-- Previous Lesson]   [Intro] [1] [2] [3] [4] [5] [6] [7] [8] 9 [10] [11]   [Next Lesson -->]

 


Reviewed/Updated: March 20, 2007