Skip to main content

Use alternative keys for portability

A deployment that works on your developer database should work unchanged against UAT and production. Identity columns make that hard; the same product might be row 42 in dev, row 108 in UAT, and row 3 in prod. Alternative keys tell DataStar to match on a stable business key instead of the identity.

The problem​

Consider a table like this:

ID (identity)PRODUCT_CODEPRODUCT_NAME
42SKU-100Widget

In dev, the widget is row 42. In UAT it's row 108. A deployment that INSERTs ID=42 into UAT will either fail (if 42 is already taken) or collide with an unrelated row.

The fix​

Mark the identity column as Transient="true" so DataStar won't try to port its value across environments. Then mark the business key with AlternativeKey="true" so MERGE matches rows on that key instead:

<Table Id="1" Name="PRODUCTS">
<Column Name="ID" PrimaryKey="true" Transient="true" InsertExclude="true"/>
<Column Name="PRODUCT_CODE" AlternativeKey="true"/>
</Table>

Now the generated script:

  • MATCH rows by PRODUCT_CODE = 'SKU-100', not ID = 42.
  • INSERT without specifying ID (the target's sequence generates a new one).
  • UPDATE every other column as usual.

Deploy to any environment, get the same result.

When you need a generator​

If the identity isn't auto-generated (e.g. it comes from a sequence the application owns), you can tell DataStar how to produce the next value:

<Column Name="ID"
PrimaryKey="true"
Transient="true"
Generator="EXEC dbo.GET_NEXT_ID 'PRODUCT', @ID output"/>

DataStar inlines the generator in the INSERT branch of the MERGE so a fresh ID is produced on the target.

Compound alternative keys​

If a single column isn't unique enough, mark several columns:

<Table Id="1" Name="RATE_CARD">
<Column Name="ID" PrimaryKey="true" Transient="true" InsertExclude="true"/>
<Column Name="REGION_CODE" AlternativeKey="true"/>
<Column Name="PRODUCT_CODE" AlternativeKey="true"/>
<Column Name="EFFECTIVE_DATE" AlternativeKey="true"/>
</Table>

MERGE will match on all three together.

An alternative key must identify one row​

Where the target generates a table's primary key (Transient="true", or a Generator), the deployed script finds each row's key by its alternative key. If that key matches more than one row, rows take each other's generated keys: the deployment could overwrite one row with another's values, fail part way, or succeed by luck.

So since 3.1.7, a script starts by checking the alternative keys of every such table against the target, and stops before any table changes if a key matches more than one row. On SQL Server:

Error: Table [PRODUCTS]: more than one row holds PRODUCT_CODE = SKU-100, so which row each change means cannot be told. Make the key unique, or correct the template's alternative key, then deploy again.

Oracle reports the same message as ORA-20003. A compound key names each of its columns.

Rows are counted across the data and the target:

Rows with the key in the dataRows with the key in the targetResult
10 or 1Deploys
2 or more1 or moreStops
12 or moreStops
2 or more0Stops: they would insert, and the next deployment would stop on them

The key is compared the way the database compares it, so on a case-insensitive SQL Server database SKU-100 and sku-100 are the same key. A NULL in a key column matches nothing, so it is not counted.

The check is in every full data script on SQL Server and Oracle, including a script inflated from a data document. A changeset has its own check, which reports such a row as a conflict. A table whose primary key the target does not generate has no such check.

To find duplicates before deploying, run this over the whole table in each database you deploy to, not only over the rows the template extracts. For a compound key, list every key column:

SELECT PRODUCT_CODE, COUNT(*) AS ROWS_HOLDING_IT
FROM PRODUCTS
WHERE PRODUCT_CODE IS NOT NULL
GROUP BY PRODUCT_CODE
HAVING COUNT(*) > 1;

Then make the key unique, or correct the template if the column is not really what identifies a row.

When not to use alternative keys​

  • The primary key is already stable. A natural string key like COUNTRY_CODE = 'GB'. Just mark it PrimaryKey="true" and you're done.
  • You don't care about the ID surviving deployment. If the identity is referenced only inside the same script, transience isn't a problem. Alternative keys become mandatory when other tables reference this one by ID (in which case you also need lookups).

Verifying the script​

After declaring alternative keys, generate a draft and inspect the MERGE:

  • The ON clause should join on your alternative-key columns, not the identity.
  • The INSERT column list should omit the identity column.
  • The UPDATE set clause should cover everything else.

If the identity column still appears where it shouldn't, double-check InsertExclude="true" and Transient="true" are both set.

See also​