When comes to database indexes and constraints, it is always better to provide unique names for each and every constraint and index we create for better management. However there can be situations where there are already created indexes or constraints where we have not named them explicitly but having system generated names. In such situations, managing them would be little bit difficult where we have to put some extra effort to identify which index or constraint it is that we need to manage.
In this example I am comparing dropping a named index and an unnamed index in IBM DB2 where the index is an auto generated one for a unique constraint that we have defined.
First let’s see the named constraint/index scenario. Here we create the Student table that has a primary key constraint and two unique key constraints. All three constraints are provided with names for manageability.
db2 "CREATE TABLE STUDENT
(
ID CHAR(5) NOT NULL,
NAME VARCHAR(50),
SSN CHAR(9) NOT NULL,
ACCOUNT_NUMBER CHAR(10) NOT NULL,
CONSTRAINT PK_STUDENT PRIMARY KEY (ID),
CONSTRAINT UNQ_SSN_STUDENT UNIQUE (SSN),
CONSTRAINT UNQ_ACC_NO_STUDENT UNIQUE (ACCOUNT_NUMBER)
)"
|
Once we create the table in DB2, we can view the constraints created for the table using the following query.
db2 " SELECT NAME, CONSTRAINTYP from SYSIBM.SYSTABCONST WHERE TBNAME = 'STUDENT' "
|
Here we can see the three constraints we have created for the Student table where the CONSTRAINTYP column shows ‘P’ for Primary Key and ‘U’ for unique key. We can see that the names for the constraints are same as the names we have defined for each constraint.
In IBM DB2, when we create a primary key or a unique key constraint, it automatically creates indexes for each constraint. We can see the indexes using the following query.
db2 "SELECT NAME, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='STUDENT'"
|
Here since we have defined the constraints by providing names for each of them, the names of the indexes are also similar to the names of the constraints. The COLNAMES column shows on which columns the constraint is defined.
Since we have provided the names for the constraints and the automatically created indexes have the same names as constraints, we can easily identify which index is associated with which constraint. However we we need to verify the relationship, we can use the following query where CONSTNAME is the name of the constraint and BNAME is the name of the index associated with it.
db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT'"
|
Following diagram shows the relationship of each constraint and it’s index.
Now if we need to drop one index created for a unique constraint, we cannot straight away drop the index. If we need to drop the auto created index in DB2, we need to drop the particular constraint by providing the name of the constraint [1].
db2 "ALTER TABLE STUDENT DROP UNIQUE UNQ_ACC_NO_STUDENT"
|
This will drop the constraint successfully. Since we knew the name of the constraint as well as the index, it was easy to drop the index/constraint without any issue.
However if we had not defined names for each constraint, it would be little bit difficult to drop the index by dropping the constraint as we do not exactly know the name of the constraint that is associated with the particular index.
Now let’s consider the same Student table where we do not provide names for the constraints we define.
db2 "CREATE TABLE STUDENT
(
ID CHAR(5) NOT NULL,
NAME VARCHAR(50),
SSN CHAR(9) NOT NULL,
ACCOUNT_NUMBER CHAR(10) NOT NULL,
PRIMARY KEY (ID),
UNIQUE (SSN),
UNIQUE (ACCOUNT_NUMBER)
)"
|
We can run the following query and see what are the constraint created for the Student table.
db2 "SELECT NAME, CONSTRAINTYP from SYSIBM.SYSTABCONST WHERE TBNAME='STUDENT'"
|
We can see that DB2 has generated some random name for each constraint. We can run the following query to find out the names of the indexes that are automatically created for those constraints.
db2 "SELECT NAME, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='STUDENT'"
|
Now if we check the names of the indexes created, those have different values than their associated constraints. By looking at the COLNAMES column, we can identify the index that is associated with a particular constraint, referring to the table definition. However since we have two unique constraints here, we cannot directly identify a particular unique index just by looking at the names of the constraints.
If we try to drop one index created for a unique constraint using the following command [2], it will give an error.
db2 "DROP INDEX SQL160223082953340"
|
That is because in IBM DB2, automatically created indexes for constraints cannot be dropped directly [1].
Now let’s see how we can identify a particular constraint associated with a particular index. Here I need to find the one unique constraint. I can provide CONSTRAINTYP = 'U' and list down all the unique indexes on the Student table.
db2 "SELECT NAME from SYSIBM.SYSTABCONST WHERE TBNAME='STUDENT' AND CONSTRAINTYP = 'U'"
|
We still do not know which constraint here is linked to the particular index we need to drop. For that, we can run the following query that shows the association of the index with the particular constraint.
db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT'"
|
Since we already know the name of the index we need to drop (BNAME column in above result), referring to that we can identify the name of the constraint.
The relationship of the constraint and the automatically created index can be shown as below. Note that the names of the indexes and constraints are system generated (random) and if you try out the same scenario, you will get different names.
We can even add a where clause to find the exact constraint name we need to drop. Here the index name 'SQL160223082953340' is the name of the index created for theunique constraint on ACCOUNT_NUMBER column of the Student table.
db2 "SELECT CONSTNAME, BNAME FROM SYSCAT.CONSTDEP WHERE TABNAME = 'STUDENT' AND BNAME = 'SQL160223082953340'"
|
Now since we know the name of the constraint, we can drop the constraint using the following command where SQL160223082953380 is the name of the constraint.
db2 "ALTER TABLE STUDENT DROP UNIQUE SQL160223082953380"
|
This will automatically drop the index also which is created for the constraint. After dropping the constraint, we can verify if the index is also dropped successfully.
With above two examples, we can see that it eases the management of constraints and indexes when we provide names for them at the time we create them. If not later we need to put some extra effort to identify the indexes and constraints separately.
References
Tharindu Edirisinghe
Platform Security Team
WSO2
