728x90
Question
Forcing a named index to be used as the index associated with a primary key or a foreign key. Here is how.
Cause
System generated names for primary and foreign key indexes and constraints.
Answer
There are times that you may want to specify the name of the index associated with a foreign key constraint or a primary key constraint rather than having one generated by the engine. You may, for instance, want to use the index name as part of an optimizer directive to be used on the same table in more than one instance.
To specify a name for the index associated with a primary key constraint or a foreign key constraint do the following:
- 1. Create the table without including the PRIMARY KEY or FOREIGN KEY clause.
2. Create a unique index on the columns used by the primary key or foreign key you want to create. This index will be the one that will be used by your primary key or foreign key constraint.
- Example:
If you are going to create a primary key on the columns col1 and col2 of a table named tab2 and you want the index to be named ixtab2 you would use this statement to create the index:
- CREATE UNIQUE INDEX ixtab2 on tab2(col1,col2);
3. Alter the table to add the primary key or foreign key constraint. The engine will detect that a unique index already exists on the columns and use that index automatically.
Example:
Continuing the previous example, the following ALTER TABLE statement would create primary key constraint that uses the index ixtab2:
- ALTER TABLE tab2 ADD CONSTRAINT PRIMARY KEY(col1,col2);
If you place both a primary key constraint and a foreign key constraint on the same columns of a table then both constraints will use the same index.
728x90