Dynamics NAV 2013 stores its data in a Microsoft SQL database. Previous versions of Dynamics NAV could either use a Microsoft SQL database or a native database for Dynamics NAV. The native database has been discontinued and is no longer available.
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.
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 field Sell-to Customer No. is related to the table Customer.
The relation is stated in the TableRelation property of the field. Sell-to Customer No. is related to the primary key field of the table Customer.
A relation is established for three important purposes, and two of them are related to data integrity:
- To establish data integrity: If
TableRelationis defined, only values existing on the related table will be allowed to be written in the field. That is, you cannot create a sales invoice for a customer that does not exist.This rule can be omitted if
ValidateTableRelationis set toNo. - 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 tables that have
TableRelationwith the first table. This means that if you rename a customer, all existing sales invoices will change itsSell-to Customer No.field value so that the sales invoice points to the renamed customer (and not to the old value ofCustomer No.). - To enable the lookup functionality: If
TableRelationis 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.
TableRelation properties may be as simple as the one shown for the field
Sell-to Customer No. in the table Sales Header, but they can also be more complicated. Conditional TableRelation properties can be defined, or you can apply filters to the relation.
TableRelation of the field No. in the table Sales Line is an example of a conditional TableRelation.
It's such a long TableRelation value that it is even difficult to 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 property TableRelation.
Now we can clearly see that the field No. in the table Sales Line is related to several different tables depending on the value of the Type field.
An example of TableRelation with a filter can be found in the field Ship-to Code from the Sales Header table.
In this Table Relation 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 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 on the OnDelete() trigger of most tables. In this trigger, conditions are usually checked to prevent the user from deleting certain information.
On the OnDelete() trigger of the table Location, some conditions are checked using the function WMSCheckWarehouse. 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.