Advanced Database – Create, modify, delete a one-to-one relationship between tables
For this section we need to create a new table.
Create a new table in design mode, with the two fields shown for the cost of repair jobs.
For the Cost field, select Decimal data type, set decimal places to 2, then click the ‘…’ icon next to Format example, in field properties.
Select Currency and choose a format that shows pounds and pence e.g. £1,234.00
Save the table with the name ‘Job Costs’ and close it.
Enter values for the cost of the various jobs.
This data could have been included on the main jobs table, but sometimes it is better to keep data in a separate table.
There are many reasons for doing this. You may want to split up a large table, keep some data separate for security reasons or if the second table has optional data that isn’t used very often.
As we have created a separate table for job costs, we need to link this to the main jobs table.
To do this we use a one-to-one relationship. Click Tools-Relationships in main window.
We are going to link the Job No primary key field from the primary table (Jobs) to the Job No foreign key field in the secondary table (Job Costs).
This is called one-to-one as there is only one record (row) for each Job No in each table.
After clicking on Relationships an ‘Add Tables’ box appears.
Select Jobs, click ‘Add’ then select Job Costs and click ‘Add’.
This adds both tables to the Relation Design window. Click ‘Close’ in the Add Tables box.
Leave the Relation Design window open.
Click and hold the Job No field in the Jobs table, then drag to the Job No field in the other table.
This creates a one-to-one relationship between them. (hence the 1 at each end)
This allows you to create queries that will reference between the two tables.
E.g. you can run a query that lists Job No, Car Reg No and Cost, although Cost is in a different table.
To modify the relationship, right click on the joining line and select Edit.
This opens a relations dialogue box, where you can change the fields involved.
The update options specify what happens when you make changes to the primary key, we’ll discuss this when we talk about referential integrity later in the course.
To delete the relationship, simply right-click the joining line, then click ‘Delete’.