Error: "Table 'X' does not have a primary key. You must specify one or more fields."
Symptoms
When a user is trying to create a list of records from the data warehouse using Jet Reports, the following error appears:
Complete text of error message is:
Table '{table name}' does not have a primary key. You must specify one or more fields.
Cause:
The cause of this error has to do with the Physical Valid Table feature being disabled in the Jet Data Manager for the specified table. This setting is disabled by default for all standard projects in order to reduce the amount of disk space occupied in the data warehouse as well as decreasing the amount of time it takes to populate the tables in the data warehouse.
When Physical Valid Table is disabled, the Jet Data Manager does not create a physical table for the Table_V object but, instead, creates this object as a SQL view. Since it is a SQL view, it has no primary key and, therefore, Jet Reports cannot create a record key on the table.
Resolution
In order to enable the Physical Valid Table in the Jet Data Manager, a user with access to the Jet Data Manager should open the program and follow the steps below:
- Navigate to the Data tab
- Expand the Data Warehouse node
- Right-click the desired table and navigate to Advanced -> Advanced Settings
- Check the box for "Physical Valid Table" (See screenshot below)
The table will then need to be deployed and executed in the Jet Data Manager so that the physical table object is created in SQL instead of using the SQL view. This table will automatically have a primary key defined on the DW_ID field, which is an identity field in every table. Since there is now a primary key defined, Jet Reports will be able to create records keys based on the records in this table.