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.

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​

Last updated on