Key Fields and Examples

Key Fields are important in Database tables as they prevent duplicate records which can lead to difficulties. They also help with the efficiency of looking up data as databases are optimized to quickly look up records based on unique keys, which speeds up queries.

So, what is a Key?

Key: This is the main key of your table. It's like a unique ID for each row in your database table. Just as no two people have the same social security number, no two rows can have the same primary key. It ensures that each entry in your table is unique and can be found quickly

Example of using Key Fields in your database table:

Let’s say you have a sign-in sheet at two different hospitals: Hospital A and Hospital B. Each patient signs in with their patient ID, and we record which hospital they visited. So, Patient 01 can have records Hospital A and Hospital B but cannot have more than one record at each Hospital, like the table below:

• PatientID (Key): This is still the patient's unique identifier, like their name on the sign-in sheet.

• HospitalID (Key): Indicates which hospital the patient visited, like the location column on the sign-in sheet.

• Reason: This is the purpose of the visit, like a note that the patient might leave about why they came in.

Without the addition of a third key field, the example above does not make a lot of sense, why can’t Patient 01 visit Hospital A again? This is because there can’t be a duplicate of Patient 01 at Hospital A as key fields do not allow that. This is an example of having 2 key fields in your database table.

To allow for a more than one visit per patient, a third key field needs to be set up. Let’s call it VisitDate:

• VisitDate (Key): This is the new column we've added. It's like the date next to the patient's name, showing us exactly when they visited.

Notice, Patient 01 visits Hospital A twice which, before we added the third key field, would not be possible. Because the VisitDate is a different value, it makes the entire entry unique due to VisitDate being a key field.

In this table, the addition of the third key field VisitDate allows for multiple visits by any Patient as the date of the visit will make each entry unique.

This is how the table would look in an IguanaX VDB:

Open

Keys are important for using conn:merge{} when Merging Records from a VDB table to a database.