| Contents: | Main | Chapter | See Also: | Getting Started Manual | Programmer Manual | |||
Using the Key Definition option, VA FileMan allows you to define keys on a file or subfile. A key is a group of fields that, taken collectively, uniquely identifies a record. All fields in a key must have values (must not be null) and those values, taken together, must be unique across all records in the file or subfile. VA FileMan enforces KEY INTEGRITY whenever records are added or edited.
Exactly one key in a file must be designated the PRIMARY KEY. All other keys are SECONDARY KEYs. While VA FileMan enforces the integrity of both primary and SECONDARY KEYs, the PRIMARY KEY is VA FileMan's principal means of looking up entries in the file. VA FileMan will prompt for lookup values for each of the PRIMARY KEY fields, and will consider a record a match only if it matches all of the lookup values. The .01 field should be a part of the PRIMARY KEY.
Keys are also useful when transporting data to another system using the Kernel Installation and Distribution (KIDS) system. Since the key fields uniquely identify a record, it is easy to decide whether a record being brought in to the target system needs to be merged to a record that already exists, or whether it is a new record.
Associated with each key is a Uniqueness Index, a regular, New-Style cross-reference. The Uniqueness Index helps VA FileMan enforce KEY INTEGRITY and is used during lookup. When you create a new key, you can have VA FileMan create a new Uniqueness Index automatically for you, or you can select an existing index to be the Uniqueness Index of the key. The index you select, though, must meet the following criteria:
To create a key, proceed in the following manner:
Select OPTION: UTILITY FUNCTIONS Select UTILITY OPTION: KEY DEFINITION MODIFY WHAT FILE: ZZPATIENT// Select Subfile: There are no Keys defined on file #16026. Want to create a new Key for this file? No// YES Enter a Name for the new Key: A// RET Creating new Key 'A' ...
You will then be taken into Screen Mode (i.e., ScreenMan form) where you can edit the properties of the key. Enter a single question mark ("?") or two question marks ("??") at any prompt for additional help.
Number: 5 EDIT A KEY Page 1 of 1 --------------------------------------------------------------------- File: 16026 Name: A Priority: PRIMARY KEY FIELDS: ========== Field Seq No. File Field Name ----- ------- ---- ---------- Uniqueness Index: Index Details... _____________________________________________________________________ COMMAND: Press <PF1>H for help Insert |
On this screen, in the KEY FIELDS section, you can select the fields you wish to include in this key, and assign each field a sequence number. The sequence number determines the order in which the fields appear as subscripts in the Uniqueness Index. If you select the key fields in this manner, leave the Uniqueness Index field blank. When you exit the form, VA FileMan will prompt you for a name for the Uniqueness Index, and then give you the option of building the index now.
I'm going to create a new Uniqueness Index to support Key 'A' of File #16026. Index Name: C// One moment please ... Do you want to build the index now? Yes// Building new index ... DONE!
Alternatively, you can leave the information in the KEY FIELDS section blank, and select an existing Uniqueness Index. When you exit the form, VA FileMan checks that the information in the KEY FIELDS section is consistent with the selected Uniqueness Index. If there is a conflict, you are asked for a method to resolve the conflict. In this case, select Option #2, "Make Key match Uniqueness Index," as shown below:
The Key fields and the fields in the Uniqueness Index don't match.
Select one of the following:
1 Re-Edit the Key
2 Make Key match Uniqueness Index (also selected on up-arrow)
Enter response: 2 Make Key match Uniqueness Index (also selected on up-arrow)
Modifying fields in Key ... DONE!
To edit a key, identify the file or subfile you wish to edit. VA FileMan will display the cross-references on the file and offer you the choices of Edit, Delete, or Create.
Select OPTION: UTILITY FUNCTIONS
Select UTILITY OPTION: KEY DEFINITION
MODIFY WHAT FILE: ZZPATIENT//
Select Subfile:
Keys defined on file #16026:
A PRIMARY KEY Uniqueness Index: C
Field(s): 1) NAME (#.01)
2) SSN (#.02)
Choose V (Verify)/E (Edit)/D (Delete)/C (Create): EDIT
Which Key do you wish to edit? A//
You will then be taken into Screen Mode (i.e., ScreenMan form) where you can edit the properties of the key. Enter a single question mark ("?") or two question marks ("??") at any prompt for additional help.
The following dialogue shows how to delete a key.
NOTE: You are also given the option of deleting the Uniqueness Index of the key.
Select OPTION: UTILITY FUNCTIONS
Select UTILITY OPTION: KEY DEFINITION
MODIFY WHAT FILE: ZZPATIENT//
Select Subfile:
Keys defined on file #16026:
A PRIMARY KEY Uniqueness Index: C
Field(s): 1) NAME (#.01)
2) SSN (#.02)
Choose V (Verify)/E (Edit)/D (Delete)/C (Create): DELETE
Which Key do you wish to delete? A//
Are you sure you want to delete the Key? No// YES
Key 'A' of File #16026 deleted.
Do you want to delete the 'C' Uniqueness Index (#6) on File #16026 previously
used by Key 'A' of File #16026? YES
Index definition deleted.
Do you want to build the index now? Yes//
Removing old index ... DONE! When you verify the integrity of a key, VA FileMan checks that all fields in the key have values (are not null), and that those field values, taken together, are unique across all records in the file. Any problems are reported. You can also save the entries that violate KEY INTEGRITY in a template.
Select OPTION: UTILITY FUNCTIONS
Select UTILITY OPTION: KEY DEFINITION
MODIFY WHAT FILE: ZZPATIENT//
Select Subfile:
Keys defined on file #16026:
A PRIMARY KEY Uniqueness Index: KEYA
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//
STORE THESE ENTRY ID'S IN TEMPLATE:
DEVICE: HOME// Telnet terminal
KEY INTEGRITY CHECK DEC 31, 1998 09:23 PAGE 1
---------------------------------------------------------------------
Key: A (#5), File #16026
Uniqueness Index: KEYA (#6)
ENTRY # NAME ERROR
------- ---- -----
1 SMITH,JOHN Duplicate Key A (#5)
2 SMITH,JOHN Duplicate Key A (#5)
3 DOE,JANE Missing Key Field(s):
SSN [16026,.02]
In this example, records #1 and #2 have the same key, and record #3 is missing a value for SSN (field #.02).
Reviewed/Updated: March 4, 2007