Modify Primary Index

[Ken Pfeiffer]

I am trying to modify a primary index on a table,
by adding another key field. It would be nice to
modify the existing index and just add the new
field, but from a bit of research I have done it
appears that the only way to do this is to
drop the existing index and create a new one.

The problem I am having is that when I attempt
to drop the index with DROP INDEX RDB$PRIMARY143
I get an error "cannot delete index used by an integrity
constant" and the operation fails. I suppose this is
because the index contains a field identified as
NOT NULL.

Any ideas how to add another field to an existing
primary index? I'd appreciate any help with this.

Thanks!

[Bill Todd [TeamB]]

Ken Pfeiffer wrote:

> The problem I am having is that when I attempt
> to drop the index with DROP INDEX RDB$PRIMARY143
> I get an error "cannot delete index used by an integrity
> constant" and the operation fails.

The error means that the primary index is reference by a foreign key
constraint. You will have to drop the foreign key constraint and any
other constraints that depend on the primary index before you can drop
the primary key and recreate it.

This is a classic example of why you should use a surrogate key
generated by a generator for the primary key. Had you done that then
created a separate unique constraint on the multiple data columns
adding another column to the unique constraint would be easy.

--
Bill Todd (TeamB)