About primary and foreign keys

Each set of attributes — whether it's a main set or subset — has a primary key (as shown in the figure above). For the main set, the primary key is a globally unique identifier for a business object instance. For subsets, however, primary keys are only unique within the parent instance (most often the main set). This means that, though there can only be one main set instance having a given key attribute value, subset instances belonging to different main set instances can have the same key attribute value.

Each attribute set has a main table. For the main set, this is the database table to which the primary key is mapped. For subsets, this contains the relation to the parent set. In addition, there can be other tables to which the attributes can be mapped. Foreign keys are used in situations where a relation exists from the main table to one of the other tables and the field containing the relation in the non-main table is not mapped to an attribute. In this case, the value of the relation field in the main table is determined indirectly by writing to another attribute that is specified as a foreign key.

For example, if we consider the "Campaign" business object in Infor e-Commerce Development Studio. The main table is named "Campaign" and there are mappings to the table named "Category" (among others). The relation to the "Category" table uses the field named "ID" but it is the field called "key" that is exposed as a foreign key attribute called "Category ID". When reading, the "Category ID" gives the value of the key field in the "Category" table but when writing, the ID field in the "Category" table is set to the value found in the record that has the written value in the key field.

As for primary key attributes, the idea is that foreign key attributes are mapped to table fields that contain unique values. Otherwise, the semantics of the operation described above would not be well defined.

The field updated when writing to a foreign key attribute is determined by following the table relation from the table to which the foreign key is mapped back to the main table.

The reasons for which the foreign key mechanism has been introduced are:

  • Performance. It is possible to implement relations between tables in identity-generated integer fields, even though a significantly larger text field is used as a key. In the above example with the "Category" table, the key field is 40 characters long while the ID is an integer — this means far less work for the database when it needs to handle the relation using searches.

  • Interface scenarios. It is easier to interface data from other systems — e.g. M3 BE — that do not have the same primary keys, since the attribute containing the primary key in another system can now be marked as a foreign key.

Note: The four characters @ = + and " are not allowed in any primary key string, that is, a string supplied to a primary key. An entity with a primary key containing one or more of these characters cannot be updated from a presentation page.