Mapping a form to the database requires the existence of either a key column or an identity column. Therefore, the user has two options to cover this requirement.
If you are shredding to a table that has an identity auto-increment column, you can leave the “Key” checkbox in DAT’s database mapping unchecked. The DBXL Web Service will fetch the identity key information from the database and will let SQL automatically set the value.
In cases where a column has not been specified in your SQL table as an identity column, DBXL will let you assign a column to act as your “primary” key. Ideally this should be an actual primary key, though this is not strictly necessary. In order to specify a “key” choose a column that will serve as the primary key by checking the checkbox “Key” when creating your mapping to a table in the Database tab in DAT. DBXL will assume that data mapped to this column is unique and will use it to identify and retrieve data shredded to the database.
If the table does not have an identity column, and the user does not designate a column as the “key” column, DAT will return a validation error to the user.
Example
In the following example, SampleTable doesn’t have an identity column in the database. After mapping the table and columns, leaving the column “Key” field unchecked, and clicking Save, user gets a validation error. See Figures 1 and 2.
Figure 1
Figure 2
To further illustrate this, the following table describes different scenarios wherein users should or should not expect to receive this error:
To prevent this error, make sure every table in your mapping meets at least one of the following criteria:
-
The table has at least one identity column (in the database)
-
The table has at least one column marked as a Key column (in DBXL)
-
The table has at least one foreign key specified to a parent table (in DBXL)
If a user tries to shred a document when the above requirement is not satisfied, an error stating that shredding failed will be displayed. The data will not be stored in the database. Note that the document is always saved to DBXL, and the user can attempt to re-shred to SQL once the shredding errors are corrected.
