The Dynamics NAV database

Dynamics NAV 2016 stores its data in a Microsoft SQL database or Azure SQL. NAV 2009 and the earlier versions of Dynamics NAV used either a Microsoft SQL database or a native database for Dynamics NAV. The native database has been discontinued and is no longer available because it's antiquated and has limitations.

The database used by Dynamics NAV is a relational database but it does not fully implement the referential integrity concept that ensures that relationships between tables remain consistent. In Dynamics NAV, data integrity is maintained partially by the database engine itself and mainly by code. Sometimes, it is not even maintained.

Tip

When developing new Dynamics NAV functionalities, consider data integrity inside your analysis and design work.

The TableRelation property

The NAV Service Tier (NST) uses the TableRelation property of fields in tables to maintain data integrity.

There are plenty of fields in Dynamics NAV tables that are related to other tables. In a sales invoice, for instance, the Sell-to Customer No. field is related to the Customer table.

The relation is stated in the TableRelation property of the field. Sell-to Customer No. is related to the primary key field of the Customer table.

A relation is established for three important purposes, and two of them are related to data integrity:

• To establish data integrity: If TableRelation is defined, only values existing on the related table will be allowed to be written to the field. That is, you cannot create a sales invoice for a customer that does not exist.

  This rule can be omitted if ValidateTableRelation is set to No.

• To maintain data integrity: If a value is changed in the primary key fields of a related table, the change will be propagated to all the tables that have TableRelation with the first table. This means that if you rename a customer, all the existing sales invoices will change their Sell-to Customer No. field value so that the sales invoice points to the renamed customer (and not to the old value of Customer No.).
• To enable the lookup functionality: If TableRelation is defined for a field in a table whenever you are editing the value of that field, the system will allow you to pick up one of the possible values by showing a drop-down list.

The TableRelation properties may be as simple as the one shown for the Sell-to Customer No. field in the Sales Header table but they can also be more complicated. Conditional TableRelation properties can be defined, or you can apply filters to the relation.

TableRelation of the No. field in the Sales Line table is an example of a conditional TableRelation.

It's such a long TableRelation value that it is difficult to even read and understand in the TableRelation property. To take a better look at it, click on the Assist Edit button that appears at the rightmost part of the Value column for the TableRelation property.

An example of TableRelation with a filter can be found in the Ship-to Code field from the Sales Header table.

In this TableRelation table, a filter is applied, so we can only select Ship-to Addresses belonging to the customer for whom the sales document is created.

Coded data rules

Coded data rules are written in table and field triggers. They are used to enforce data integrity when it cannot be obtained with simple mechanisms, such as field types or table relations.

One of these data rules that you can see all over the application can be found in the OnDelete() trigger of most tables. In this trigger, conditions are usually checked to prevent the user from deleting certain information.

In the OnDelete() trigger of the Location table, some conditions are checked using the WMSCheckWarehouse function. If some conditions make it impossible to delete the location, an error message will be shown and the action will not be taken.

In the OnDelete() trigger of tables, code also exists to ensure that related information is deleted as well. In the example, transfer routes for the location that is being deleted are deleted as well. The WMSCheckWarehouse function has also deleted the zones, bins, and bin contents of the location that was deleted.

If you're interested in learning more programming related aspects of Dynamics NAV, please refer to Programming for Dynamics NAV, also published by Packt Publishing.