Editing Relationships

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:

1. Practice on some simple examples first

1. 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. Select the 'Entities' tab in the Schema Editor and click on the 'New Relationship...' icon ( ) in the Schema Editor toolbar. From the popdown menu choose the appropriate item:

• New One-to-One Relationship: for creating simple One-to-One relationship

• New Many-to-One Relationship: for creating simple Many-to-One relationship

• New Many-to-Many Relationship: for creating a complex Many-to-Many relationship

• Existing Foreign Key: for promoting an existing foreign key constraint to a relationship

  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 'STRUCTURES2SAMPLES'.

Step 5

Go to the Constraints tab and make any changes that are needed here. This tab defines the database foreign key constraint. The foreign key constraint will only be added if the 'Create DB constraints' checkbox is checked on the first tab.

Step 6

Click on 'Next' and the Relationship should be created. But before you can finish, the wizard enables you to choose which edges to add based on the previously created relationship. The following screenshot shows you an example of adding a simple One-to-One relationship.

After clicking 'Finish', the relationship will be implemented. 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. The foreign keys are shown in the 'Tables' tab under the table to which they belong.

To promote a foreign key to an Relationship select the foreign key and click on the 'Promote to Relationship' button in the toolbar. Alternatively use the third option described above for creating a relationship. 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 clicking 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.

Editing Master-Detail retrieval limit

In a database with a master-detail relationship, a retrieval limit can be set. This property determines the maximum limit of selected rows in the master table for which details will still be shown in the details table.

To set up the master-detail retrieval limit, in the Schema Editor open the Entities section where you will see the Relationships node.

After clicking the content of this node, click the Extra Attributes tab in the panel on the right. This will show you current setting of the master-detail retrieval limit, which can be set either by double-clicking, or clicking the Edit button. By default, the limit is set to "Unlimited".