By default, EDW 3NF tables in WhereScape RED do not have an artificial (surrogate) key. Artificial keys can be added manually.
The quickest way to do this is to add an extra column to the EDW 3NF table by using either Add Column or Copy Column.
Edit the properties of the new column to have the correct name and order, source table and column, datatype, key type and flags.
Specifically:
- The Column Name and Source Column should be the same.
- The Source Table should be empty.
- The Data Type should be:
- DB2: integer generated by default as identity (start with 1, increment by 1)
- SQL Server: integer identity(0,1)
- The Key Type should be 0.
- Only the Numeric and Artificial Key flags must be set.
The following example shows a manually added artificial key column:
This artificial key normally, and by default, starts at one and progresses as far as is required.
A WhereScape standard for the creation of special rows in the EDW 3NF tables is as follows:
Key value | Usage |
|---|---|
1 upwards | The standard artificial keys are numbered from 1 upwards, with a new number assigned for each distinct EDW 3NF table record. |
0 | Used as a join to the EDW 3NF table when no valid join existed. It is the convention in the WhereScape generated code that any EDW 3NF table business key that either does not exist or does not match is assigned to key 0. |
-1 through -9 | Used for special cases. The most common is where an EDW 3NF table is not appropriate for the record. A new key is used rather than 0 as we want to distinguish between records that are invalid and not appropriate. |
-10 backward | Pseudo records. In many cases, we have to deal with different granularities in our data. For example, we may have a table that contains actual sales at a product SKU level and budget information at a product group level. The product table only contains SKU based information. To be able to map the budget records to the same table, we need to create these pseudo keys that relate to product groups. The values -10 and backward are normally used for such keys. |
To have a surrogate key auto-added for EDW 3NF tables, refer to Global Naming of Key Columns for details.