CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout/Adding Columns

---

Adding columns to a Table

Outcomes

Upon completion of this tutorial the learner will be able to: Add new fields to an existing database table Set the properties of the new fields to manage the existing table data Make data updates to fit the required property settings

Modifying a table

Figure 1. Decision to modify

During the development of a database you will often come across the need to change a tables design. In this example we need to alter the 'PhoneNumber' tables design. It has become apparent that we need to separate out the area code from the phone number and we need to add a country code. This modification will allow us to more easily search on the area code and be able to filter on the country code.

Managing the change

Figure 2. Modify the design

Figure 3. Causing a data error

When adding new fields to a table it may be identifed that some of the new fields should be mandatory (i.e. Entry required). This can cause problems when data already exists in the table for the new fields would not contain data. This is demonstrated when you try and save a table with a new field having the property of 'Entry required'. To make a field mandatory change its 'Entry Required' property to 'Yes'.

Figure 4. Set Entry required field to No

Figure 5. Save the modified table

To fix the problem of a column constraint (not having data in a mandatory field) change the field to allow no data by changing the 'Entry Required' property to 'No'. Save the table.

Updating the data

Figure 6. Fixing the data

Figure 7. Setting the Entry required property

Open the table and add data to the columns that will be mandatory. Close the table and re-open it in 'Edit' (design) mode and set the 'Entry Required' property back to 'Yes'.

Test your knowledge

Adding and changing columns

1. A table design should never be changed? TRUE → Incorrect, a tables design can change. Click here to review the topic.. FALSE → Absolutely Correct! 2. Which is NOT a step when adding a new mandatory field to a table that already has data? (a) Setting the primary key → Absolutely correct! (b) Changing the 'Entry required' field back to 'Yes' → Incorrect, this is a step. Click here to review the topic. (c) Adding data to the columns that will be mandatory → Incorrect, this is a step. Click here to review the topic. (d) Saving the modified table → Incorrect, this is a step. Click here to review the topic. (e) Open the table for editing → Incorrect, this is a step. Click here to review the topic. Your score is 0 / 0