To keep your data normalized and organized, the use of primary keys and business keys is very important. When you virtualize entities, you must configure and map your keys, so the information is kept in the external system considering the keys of the external data. This article mentions considerations about the configuration of the key in virtualization.
Differences between a primary key and a business key
To fully understand the configuration of a virtualized entity, you must consider the differences between a primary and a business key. A primary key is a column that uniquely identifies a record, or instance, within a table. So values are unique for each row, and cannot contain null values.
In Bizagi projects, primary keys are created automatically, so every entity in a Bizagi project has a primary key managed by Bizagi. So, if you need to set a column with unique values, for example an identification number of clients, you need to define a business key. A business key is a concept in Bizagi to define attributes as columns with unique records. See Business keys.
So the difference between a primary key and a business key in Bizagi, is that the primary key is a consecutive number defined automatically by Bizagi, and a business key is an attribute of an entity with unique values, which is defined by users.
Keys in external databases
In external databases, the concept of business key usually does not exist. What is usually configured are primary keys. Meaning that you can configure multiple keys (compound keys), to work together as unique identifiers of a record.
Mapping keys in virtualization
When you are mapping attributes in the virtualization configuration, see Using advanced configuration. You have to make sure that you map the attribute in Bizagi with a column in the external table that is considered as the primary key. This attribute must be also set as a business key by the user. The following diagram show how it must be mapped:
So the final virtualization advanced configuration looks like this:
And the properties of the table:
Creating new records in the external table from Bizagi
When you use a search control you can add records from the control using the Allow new records property. When you associated the search control with a virtualized entity, new records created from the form, are also created in the external table. Bizagi alert users when the value of an attribute set as a business key, or an attribute mapped with a primary key is empty.
When the attribute set as business key is empty
When the attribute set as a business key is empty in the add form, Bizagi displays the following message:
When the attribute mapped with primary key is empty
If the attribute that you mapped with the external table primary key is empty, Bizagi displays a warning message, because primary keys cannot be null in the external table:
When the primary key is auto-generated
It is common that external applications manage primary keys with consecutive numbers generated automatically. When you map an attribute with a primary key that is generated automatically, you have to activate the Attrib auto-fill option:
There are two options to generate an attribute automatically:
•Using a unique identifier.
If you want to virtualize an entity with a uniqueidentifier, you have to configure it with the advanced Virtualization configuration. You cannot use the virtualization wizard.
•Using an attribute as an identity with increment.
It is important to bear in mind that you do not need to include attributes that are auto-fill in the external source, within add forms of search controls.
When two or more related entities are virtualized
When you virtualize an entity, and the entity has relationships, all its related entities must be virtualized. The reason that the foreign keys of the relationships are now handled by the external database.
Let us analyze the following example. You have to related entities Entity 1 and Entity 2, and you virtualize the Entity 1:
The foreign key with the second entity must be also part of the virtualization configuration.
In the external table, this attribute must be set as a foreign key:
Additionally it is very important that the related entity, in this example Entity 2, has the primary key virtualized and set in Bizagi as a business key:
All primary keys from the external table of the relationship must be virtualized:
If you enable adding records of the entity 1, we recommend using in the add form a search control of the entity 2, so you can make sure that the record in the entity 2 exists, and you do not have issues with nonexistent foreign keys.