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

---

Creating Primary Keys

Outcomes

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.

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

Addresses

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

Defining Keys

1. The primary key is one of the tables candidate keys? TRUE → Absolutely Correct! FALSE → 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? TRUE → Absolutely Correct! FALSE → Incorrect, click here to review the topic. Your score is 0 / 0