Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Will index name created in SQL cause problems with CRC?

« Go Back

Information

 
Article Number000089677
EnvironmentProduct: OpenEdge
Version: 11.x
OS: All supported platforms
Other: SQL
Question/Problem Description
Creating a table using CONSTRAINT and PRIMARY KEY options a primary unique index is created with a name like "SYS_001_000000000" instead of the name given to the CONSTRAINT.

If in development system the index is created as SYS_001_000000000 and the index for the same table is created on a production database as SYS_001_000001234:
  • Will this index name cause problems with CRC for 4GL applications?  
  • Will this index name cause problems with CRC when is created in different databases?
Steps to Reproduce
Clarifying Information
This the SQL command used to create a table :
 
CREATE TABLE PUB.customer ( 
    customer_id int DEFAULT 0 PRO_FORMAT '>,>>>,>>9' 
                              PRO_LABEL 'CUSTID' 
                              PRO_COL_LABEL 'CUSTID' 
                              PRO_ORDER 10, 
    customer_name varchar(100) PRO_CASE_SENSITIVE 'N' 
                               PRO_FORMAT 'X(100)' 
                               PRO_LABEL 'Customer Name' 
                               PRO_COL_LABEL 'Customer Name' 
                               PRO_ORDER 20 ,  
    CONSTRAINT customer_id_key PRIMARY KEY (customer_id) 
                               AREA "index_data"
)
AREA "data";

 
Error Message
Defect/Enhancement Number
Cause
The name of the index created with using CONSTRAINT - PRIMARY KEY is given by the system by default. 

The scenario described above will cause problems when the Constraint Key is created on a different database, since it might create the primary unique index with a different name and 4GL applications will report problems. 
Resolution
Currently there's no mechanism to control the name of the primary unique index when is created using CONSTRAINT - PRIMARY KEY on the SQL side. The system will create the primary unique index by default. 

An enhancement has been requested:

https://community.progress.com/community_groups/products_enhancements/i/openedge_database_enhancements_-_tell_us_what_youd_like_to_see/allow_specifying_a_name_to_the_index_created_when_using_a_constraint_on_the_primary_key

 
Workaround
As workaround, choose an existing index to be the default index for the ABL or create a new index.

Example:

1. Create table as it is with the same command: 
 
CREATE TABLE PUB.customer ( 
    customer_id int DEFAULT 0 
                PRO_FORMAT '>,>>>,>>9' 
                PRO_LABEL 'CUSTID' 
                PRO_COL_LABEL 'CUSTID' 
                PRO_ORDER 10, 
    customer_name varchar(100) 
                  PRO_CASE_SENSITIVE 'N' 
                  PRO_FORMAT 'X(100)' 
                  PRO_LABEL 'Customer Name' 
                  PRO_COL_LABEL 'Customer Name' 
                  PRO_ORDER 20 ,  
    CONSTRAINT customer_id_key 
               PRIMARY KEY (customer_id) 
               AREA "index_data"
)
AREA "data";

*** 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.
Notes
Attachment 
Last Modified Date5/24/2019 2:37 PM