Editing Relationships

Relationships define associations between Entities and are modelled on a database foreign key constraint, but at a higher level of abstraction. You define a Relationship in the Schema Editor. You use Relationships to build a Data Tree. See the About Relationships document for a more complete description.

Relationships are shown for each Entity. Unlike foreign key constraints which belong to just one database table both Entities involved in a Relationship know about it. Thus when you add a new Relationship it appears for both Entities. When you delete it from one Entity it also disappears from the other one.

In the Schema Editor relationships are displayed in a child node of the Entity (called 'Relationships and Keys'). Each Relationship relevant to the Entity will be listed (the Relationships node will not be present if there are no Relationships for that Entity). The directionality of the Relationship is indicated by the direction of the Relationship icon.

Like other items in the Schema Editor, if you select a Relationship its properties are displayed in the main editor panel. Most the properties are not editable. If you need different properties you should delete the Relationship and add a new one.

Understanding and using Relationships needs a high level of understanding and expertise. You are recommended to:

Practice on some simple examples first
Define your Entities and Relationships first, before you go to the effort of loading in the actual data. If you make a mistake you can easily delete the Entities and start again.

Adding a Relationship:

Step 1. Make sure you have the necessary Fields present in the Entities to define the Relationship. Typically this would involve adding a Field to the 'many' side of the Relationship that will contain the values of the IDs of the 'one' side, usually using the ID field of the entity (the CD_ID or <entity name>_ID) Fields. This Field must have the same type as the ID field (e.g. Integer).

These screen shots show the state before and after the fields for 2 Relationships were added (this follows the example used in the About Relationships help page.

Before	After

Due to the different behaviours of the different databases supported by IJC the necessary approach can be slightly different.

When adding a relationship using the Derby (local) database and the destination field is not the ID field (the primary key column) this field must be defined as requiring a value (the Required setting must be set to TRUE). This is not necessary for the other database types (but can be specified if appropriate).

Step 2. Click on the 'New Relationship' icon ( ) in the Schema Editor toolbar. The New Relationship wizard opens.

Step 3. Specify the appropriate Entities and Fields (see step 1). In the case of many-to-one Relationships the Relationship need to be from the many side and to the one side.

Step 4. Enter a name for the Relationship. Typically use a descriptive name such as 'STRUCTURES to SAMPLES'.

Step 5. Go to the Constraints tab and make any changes that are needed here. This tab defines the database foreign key constraint.

Step 6. Click on 'Finish' and the Relationship should appear for both the Entities. The screen shot below shows the results of adding the 2 Relationships. Note the directions of the Relationships.

Once all your Relationships are defined you can use them in building a Data Tree. See Editing Data Trees for details.

Promoting a foreign key

The database may contain some foreign keys that are not currently used by any IJC relationship. These might have been in the database before it was used by Instant JChem, of might have been created by Instant JChem and then the Relationship was deleted without the corresponding foreign key being removed. To see these unused foreign keys click on the 'Filter unused database artifacts' toggle () in the toolbar of the Schema Editor.

To promote a foreign key to an Relationship select the foreign key and click on the 'Promote to Relationship' button in its Editor. A wizard will open and prompt you for any necessary information.

Simple relationships

For one-to-one and many-to-one relationships you need to specify a name for the relationship.

Many-to-many relationships

Promoting foreign keys to a many-to-many relationship is more complex as two foreign keys are involved as well as a join table. Start by selecting one of the foreign keys and cliking on the promote button as for the simple relationships. When the wizard opens check the 'Use join table' checkbox and then specify the appropriate join table and second foreign key.

Adding the relationship

Click on the finish button and the relationship will be added.