While fiddling with my dozens of linked tables I’ve came across an interesting issue.
I’ve got a table with simple primary key (field 1). 1. This table is connected to SQL View, and the View is pointing to SQL Table. When I run some code against data in my table I’ve identified that there is number of records with same primary key! Well, it is not something you can see in NAV world.
I did some investigation on this, and found, that my SQL table (which wasn’t created by me, but provided by third-party organisation) has no primary key on it, therefore it is fine to have duplicate values in any (or even all fields) for some (or even all) records.
It seems Dynamics NAV Linked tables can’t handle this situation properly. Well, when you define any table in NAV it has to have a primary key, and you will expect it to be unique. But not for a linked tables. Linked tables inherits the schema defined in the SQL side, so it may have non-unique values in your primary key field.
I’ve tried to reproduce the issue – and have done it quite successfully, please have a look to pictures below:
- NAV table structure and Primary Key
- SQL table Definition
- Data I’ve put to SQL table
- Data I can see in NAV system
So, what we can do to resolve this? In ideal world we should always have same primary key in SQL table and NAV linked table. In real life it may be difficult (you may have no right to alter tables provided by third-party organisations) – so if you unable to alter SQL table – then you have to handle this on NAV side by code… Of course, I would prefer to keep same keys on both sides, in Dynamics NAV and in SQL.