CCNC/CCNC Module 5/Designing and creating tables/Defining Keys
|Defining Keys and Indexes|
Creating Primary Keys
What is 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.
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.
- 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|