CCNC/CCNC Module 5/Designing and creating tables/Defining Keys

From WikiEducator
Jump to: navigation, search
Tutorial.png Defining Keys and Indexes 

Primary Keys | Indexes | Self Assessment | Summary & FAQs

Creating Primary Keys

Icon objectives.jpg


Upon completion of this tutorial the learner will be able to:
  • Describe a Primary Key
  • Create a Primary Key
  • Discuss Candidate Keys

What is a Primary Key?

Figure 1. Defining a Primary Key

Every table in OOBase needs to have a primary key defined. The primary key is a field (or fields) that uniquely identify a record. In other words no two records can contain the same number in the primary key field. In the People table we have a field called person_id which should be set to the primary key. This means that each person will have their own unique identifying number. If you use a field such as the Name as a primary key it will probably not work as you may find people with the same name. Sometimes you can use an existing field such as employee_id number as a primary key field. If you do not have a field that is suitable to use as the primary key then you will need to create one such as person_id to act as the primary key.

Creating a Primary Key

To set a field as a primary key, click on the grey button next to the field name in and right click to activate the pop up menu. Click on Primary Key. You will see a small yellow key appearing next to the field name.

Candidate Keys

A candidate key is one or more attributes that can be used to uniquely identify a database record. Each table may have more than one candidate key. One of the candidate keys is selected as the tables primary key. When designing a table it is important to identify ALL possible candidate keys. This will help in understanding your data and allow you design options as you grow your database.

Icon present.gif
Tip: Allways include an AutoValue field
  • It is recommended that an AutoValue field be added to every table regardless of whether it is a part of your table design. This AutoValue field will automatically contain unique values for the life of the table. These value may become very useful some point in the future when you want to alter your database design.

Test your knowledge

Field Name Data Type Required Description
address_line1 Text Yes First line describing an address. Could be street name or suite number.
address_line2 Text No Second line describing an address.
city_name Text Yes The name of the city
region Text Yes The name of the region. Could be province, state, etc.
country Text Yes The name of the country
postal_code Text Yes The localized postal code or equivalent

Icon qmark.gif

Defining Keys


1. The primary key is one of the tables candidate keys?

Absolutely Correct!
Incorrect, click here to review the topic.

2. From the Addresses table design above the combination of address_line1, country and postal_code could be a?

(a) Primary Key
Almost correct, click here to review the topic.
(b) Candidate Key
Almost correct, click here to review the topic.
(c) Concatenated Key
Almost correct, click here to review the topic.
(d) Unique Identifier
Almost correct, click here to review the topic.
(e) All of the above
Absolutely correct!

3. It is a good idea to always include an AutoValue field?

Absolutely Correct!
Incorrect, click here to review the topic.

Your score is 0 / 0