Avoiding system generated index and constraint names

Problem(Abstract)

This article describes a problem which occurs when constraints and their related indexes are created and named by the default system commands, and a way to name them manually.

Resolving the problem

PROBLEM

Depending upon how you create primary/foreign key constraints, IDS will generate names for them and the indexes that are used to enforce them. These names are not intuitive (like customer_pkind, customer_fk1), which causes confusion when viewing or dropping them.

Example:

If you create a table like this:

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num)
      );

Then you will end up with an index called ' 100_1' (note the leading space), and the constraint name is 'u100_1'. These names are generated based on the tabid (customer is tabid 100) and a sequence number. The index can't be dropped directly, because the name has a leading space character.


Adding a primary key after creating the table (with alter table) hits the same generated name issue:

  alter table customer add constraint primary key (customer_num) ;

The index name is now ' 100_26' and the constraint name is 'u100_26'.

CAUSE

The program is designed to work this way.



SOLUTION

To remove the index you have to look up the constraint name, either through dbaccess:

Query Language <Select the database > Info  <Select  the table>  
           cOnstraints Primary / Reference


or with SQL like this:

  select t.tabname, i.idxname, i.idxtype, 
         c.constrname, c.constrtype
    from systables t, sysindexes i, sysconstraints c
   where t.tabtype = 'T'
     and t.tabid = i.tabid
     and i.idxname = c.idxname
     -- and t.tabname = 'TABLENAME'
  order by 1, 2  ;


and then drop the constraint:

  alter table customer drop constraint u100_1 ;



HOW TO AVOID THIS PROBLEM

You can set the constraint name of your choice by adding a constraint naming clause to the ALTER TABLE or CREATE TABLE command:

Example:

alter table customer add constraint 
primary key (customer_num) constraint cust_pk ;

create table customer
      ( customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address                 char(200),
        phone                   char(18),
       primary key (customer_num) constraint cust_pk
      );

This sets the constraint name to 'cust_pk', but the index name is still generated (to ' 100_27' or similar). 

To control the index name, you must create the index before the constraint.

Example:

create unique index cust_pki on customer(customer_num) ;
alter table customer add constraint
  primary key (customer_num) constraint cust_pkc ;

This way the index name becomes 'cust_pki', and the constraint name is 'cust_pkc' (though it is more common to set them to the same name).

When you create a constraint, it will use an existing index instead of creating one, if one exists with the right columns in the right order and the right uniqueness.

Creating the index explicitly first also means you can specify which dbspace(s) it is stored in, the fill factor, and other things.

http://www-01.ibm.com/support/docview.wss?uid=swg21255132