Modify Primary Index

Thread View: borland.public.interbase.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Ken Pfeiffer Tue, 05 Aug 2008 21:11

#191

Author: Ken Pfeiffer
Date: Tue, 05 Aug 2008 21:11

20 lines
1676 bytes


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!

Re: Modify Primary Index

#192

Author: Bill Todd [TeamB]
Date: Wed, 06 Aug 2008 03:38

19 lines
1669 bytes

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)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads