Preparing tables without primary keys

The data columns in your table might not need a primary key. To replicate tables that do not have primary keys, you can specify a unique index or add the ERKEY shadow columns.

About this task

You can specify an existing unique index or unique constraint as the replication key when you define the replicate. Use the --key or --anyUniqueKey option with the cdr define replicate or cdr define template commands.

If you create a replicated table through a grid, the ERKEY shadow columns are automatically created and included in the replicate definition.

Procedure

To add ERKEY shadow columns:

Add the ERKEY shadow columns when you create at table by using the WITH ERKEY keywords with the CREATE TABLE statement.
For example, the following statement adds the ERKEY shadow columns to a table named customer:
```
CREATE TABLE customer (id int) WITH ERKEY; 
```
The ERKEY shadow columns are named ifx_erkey_1, ifx_erkey_2, and ifx_erkey_3.
Define the replicate. If you define a replicate by using the cdr define replicate command, include the --erkey option. If you define a template by using the cdr define template command, the ERKEY columns are included in the replicate definition automatically.

What to do next

To add the ERKEY shadow columns to an existing table that you want to start replicating:

Run the ALTER TABLE statement with the ADD ERKEY clause. For example, the following statement adds the ERKEY shadow columns to an existing table named customer:

ALTER TABLE customer ADD ERKEY;

Occasionally, you might need to drop the ERKEY shadow columns; for example, if you are reverting to an earlier version of the database server.

To drop the ERKEY shadow columns from a replicated table:

Run the cdr remaster command without the --erkey option.
Run the DROP ERKEY clause with the ALTER TABLE statement.

For example, the following statement drops the ERKEY shadow columns from a table named customer:

ALTER TABLE customer DROP ERKEY;