A database table can have a special column (or set of columns) that is designated as a key. Every value in the key column(s) is unique. By designating a key in a table, you are providing the database engine with a guarantee that any search on the key will produce either zero or one row. Effectively, when a search matches a value in the key column, the row containing that key is guaranteed to be unique in the table.

A database table does not require a key. The key is effectively a hint to the database engine to improve efficiency and to guarantee uniqueness. You can still search and modify a table that has no key.

When working with the DataHub Data Logging interface, you have the option of specifying a key column. The interface will only allow single-column keys. You will not be able to specify more than one key column, and if you choose an existing table with multiple key columns, the interface will fail.

Your choice of key will depend on whether you have chosen Add New Rows or Modify Same Row for your database table.

Adding New Rows

If you choose to add (insert) new rows into your database table, you do not require a key column. Each new logging event will create a new row and populate it with the data that you have configured. Since you are continually adding rows to the table, there is no data that can be guaranteed to be unique within a column. Any one of point name, quality, value or timestamp may be repeated.

Consequently, if you do wish to have a key column in your table, it must have one important property: it must be automatically generated and guaranteed to be unique. The key value cannot be derived from a DataHub point. Most database engines support the concept of a counter or an auto-increment numeric value. If you choose to use a table containing a key, the key column must be of the appropriate auto-incrementing integer type for your database.

You can designate a key by choosing the <key> option for the Item entry of the database table when configuring a table through the Data Logging interface. If you are using an existing table that already contains a key, you must specifiy in the Data Logging interface that the column is a key column.

Modifying the Same Row

If you choose to modify a row in the database, such that any new data will overwrite the existing data in the row, you must be able to uniquely identify that row. This means that you must have a key column.

The key column can be any type, and does not need to be auto-incrementing. Since the row is overwritten whenever new data is available, no new key value is generated. It is common to supply the DataHub point name as a key, with the key field defined as a string type (typically VARCHAR, NVARCHAR or CHAR). You can do this by selecting a point in the point picker tree, then choosing the <point>; option for the Item entry of the database column, and <key> for the Property field.

If you wish to specify a key value other than a point name, type your own value into the Item entry instead of using the point name. You must then choose <key> for the Property entry.