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 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)
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!
Select this
link to test yourself on what you've learned in this lesson.