Thank you very much we will look at this

Yours Sincerely

Johannes Pretorius


At 10:22 AM 03/02/2006, you wrote:

>When using old versions, the best solutions may be in old documents.
>Your particular question is brilliantly answered in Claudios classic
>article: "The mystery of rdb$db_key"
>(http://www.cvalde.net/document/mysteriousDbKey.htm). In short, you
>should do:
>
>delete from a a1
>where a1.rdb$db_key in (
>select a2.rdb$db_key
>from a a2 join a a3 on a2.code=a3.code
>and a2.rdb$db_key>A3.rdb$db_key)
>
>I never thought I would recommend using IN (<subselect>), I always
>prefer EXISTS. Though Claudio know what he is talking about and it's a
>very long time since I used InterBase 5.6, so you better stick to what
>is known to be working.
>
>Set
>
>--- In firebird-support@yahoogroups.com, Johannes Pretorius wrote:
>> Good day all
>>
>> 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.
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>