As workaround, choose an existing index to be the default index for the ABL or create a new index.
1. Create table as it is with the same command:
CREATE TABLE PUB.customer (
customer_id int DEFAULT 0
PRO_LABEL 'Customer Name'
PRO_COL_LABEL 'Customer Name'
PRO_ORDER 20 ,
PRIMARY KEY (customer_id)
*** At this point the system primary unique index is created with the SYS_NNN_NNNNNNNNN name.
2. Create a new index with the name "customer_id_key
" on the same field (customer_id)
CREATE INDEX customer_id_key on PUB.customer (customer_id);
3. Alter table setting the new created index as default and primary index on the table:
ALTER TABLE PUB.customer SET PRO_DEFAULT_INDEX customer_id_key;
There will be 2 indexes with the same keys:
- One for the constraint and
- The primary unique index with a known name where an ABL for each with no explicit index and no search keys will use the new default index.
Exporting with sqlschema the table structure and using the SQL script to create the table on other database, the ALTER TABLE statement to set the customer_id_key index as default and primary will need to be added.
Creating 2 identical indexes will however use twice the storage space that would be needed compared to only using one index.
The needed memory used for the -B database startup parameter value when tuning for best performance might need to be set higher.
Instead of having to update 1 index for each new table record the database will need to update both identical indexes for each new table record.