Make sure they are all gone.

Select Code, Count(Code) as CodeCount
From A
Group By Code
Having Count(Code) > 1

If you have no duplicate keys, it will return nothing. If it does return something, the Code it returns are what is
duplicated.


Johannes Pretorius wrote:

> 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
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>
>

--
The information transmitted herewith is sensitive information intended only for use to the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon, this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.