Good day all
-=0-0=0-0-=-=0

We have a problem with a database of ours. It is in interbase 5.6.

We have a table, for this example called A :


CREATE TABLE A (
CODE VARCHAR(6) NOT NULL
TITLE T_TITLE,
INITIALS T_INITIALS,
SURNAME LASTNAME
)


It has an index on the one column as example

CREATE ASCENDING INDEX RD_CODE ON A (CODE)

Now after a few years we have decided to do what we where suppose to do from
the beginning and that is to ADD a primary key on the table on the CODE column

We do this as follows :

ALTER TABLE A
ADD CONSTRAINT A_PK1
PRIMARY KEY (CODE)

We then got the error :

KEY VIOLATION ATTEMPT TO STORE DUPLICATE VALUE (VISIBLE TO ACTIVE TRANSACTIONS) IN UNIQUE INDEX "RDB$PRIMARY58"

We then deleted all duplicates as follows

delete from a a1 where (select count(code) from a a2 where a1.code = a2.code)>1

We then STILL got the same error. So when then Sweeped the database and
checked Forced Writes is ON. And dit it again but still the SAME error.

We don't really know anymore from here on what to do. If we
run the following query we get NO records that indicate to be duplicated.

select * from a a1 where (select count(code) from a a2 where a1.code = a2.code)>1


If anybody can give us some ideas it will be appreciated.

Yours Sincerely

Johannes Pretorius
(Programeerings Departement- Promed)

Tel : +27 11 607-3300
Faks : +27 11 622-6939
webtuiste : http://www.promed.co.za

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you are not the intended recipient, any review, re transmission,
disclosure, copying, modification or other use of this email message or
attachments is strictly forbidden. If you receive this email message in
error, please contact the author and delete the message and any
associated files from your computer. Also the contents of this e-mail
is considered confidential.
Thank you