CCNC/CCNC Module 5/Designing and creating tables/Table Relationships
Creating table relationships
All the work done in this designing and creating tables section has been focused on table design and creation. Excepting in unique situations every table in a database will be related to another table. Setting the rules around the table relationships is done by using the OO.o relationship designer.
Designing the relationship
This first relationship we will define the rules will be the one-to-many relationship. In our example we will be describing the relationship between the People to PhoneNumbers tables. The idea behind this relationship is that a person can have more than one phone number (i.e. home, work, mobile, pager, etc.)
Adding the tables
The first step in setting the rules of a relationship is to open the Relationships designer. This is done through the Tools -> Relationships menu.
The relationship designer will start with either; a dialog prompting to add tables or a display of the existing relationships. Which gets displayed depends if there are already existing relationships. If you are prompted to add tables add both the People and PhoneNumbers tables. After you close the add tables dialog the relationship designer screen will appear. It is within this window that you will design the relationships.
The toolbar of the relationship designer has two important buttons;
Defining the relationship
To define a new relationship just drag and drop the foriegn key field from one table over top of the primary key of the other table in the one-to-many relationship. In the example provided here the person_uid from the PhoneNumbers table is dragged onto the person_uid of the People table.
Edit the relationship
To edit the properties of the relationship right click the line between the tables and choose the Edit... option.
Setting the rules
You want to make sure that the records between tables remains accurate once a relationship has been created. The way to do this is to enforce referential integrity. Editing the relationship properties is how referential integity is enforced. By default no action is taken on relationships.
By clicking on Update cascade you are ensuring that if a change is made in the Primary Key field it will make the changes in the other tables that that field is linked to.
Clicking on Delete cascade ensures that Base will delete all the records that are linked to that field. If this option is not selected you will not be allowed to delete a record that is linked to other records.
Test your knowledge