Defining and creating relationships between OpenOffice™ Base tables

A contribution by Axel Drefahl, axeleratio.com, September 17, 2016.

This post continues my preceding OpenOffice™ Base (ooBase) lessons:

1. Exploring OpenOffice™ Base: an example involving rare-earth mineral data  
2. Structuring rare-earth mineral data in an OpenOffice™ Base table
3. Modifying the column layout of an existing OpenOffice™ Base table 

We already defined a one-to-many relationship (one mineral class contains many minerals) by including a secondary key with field name MinClassID in the CesbronList table. This key points to a specific mineral class in the MineralClasses table. So far, this relationship exists in our (mental) blueprint. To create the relationship, we need to implement it into our ooBase application. This is achieved as follows:

1. If not yet done, open RareEarthMinerals_2tables.odb and go to Tools >  Relationships... to open the Add Tables dialog.
2. Add both tables, MineralClasses and CesbronList, into the empty space of the Relation Design window; then Close AddTables. 
3. Put your mouse pointer on the MineralClasses.ID field, keep the left mouse button down, move the pointer over to CesbronList.MinClassID and release the mouse button. You should now see a 1-to-n line connecting the selected fields.
4. Go to File > Save to conclude the relationship creation before closing the Relation Design window.

1-to-n connection between two tables

From now on you cannot put a value into a CesbronList.MinClassID cell which does not already appear in the MineralClasses.ID column: each mineral belongs to exactly one given class (within our design). If you enter a wrong value, you will receive a message stating an “Integrity constraint violation” error. Therefore, some experts recommend to implement relationships before starting to enter any data into the tables. But it is completely up to you in which order you proceed.