Thanks , we will try it.

Yours Sincerely

Johannes Pretorius


At 09:31 AM 03/02/2006, you wrote:

>Try this instead:
>
>select * from a a2
>where a2.code IN
>(select a1.code from a a1 group by a1.code having count(*) > 1)
>
>
>--
>Nick
>-----We Solve your Computer Problems---
>Panther, Ingres, UNIX, Interbase, Firebird - Available Shortly
>
>-----Original Message-----
>From: firebird-support@yahoogroups.com
>[mailto:firebird-support@yahoogroups.com] On Behalf Of Johannes Pretorius
>Sent: 03 February 2006 07:15
>To: firebird-support@yahoogroups.com
>Subject: [firebird-support] Funny problem with creation of Primary keys on
>table
>Importance: High
>
>
>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
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>
>SPONSORED LINKS
>Technical
><http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
>w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
>+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
>al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support Computer
><http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
>+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
>w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
>t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical support
>Compaq
><http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
>chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
>upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
>icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
>technical support
>Compaq
><http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
>upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
>=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
>technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support
>Hewlett
><http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
>chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
>upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
>icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
>technical support Microsoft
><http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
>l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
>&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
>ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
>support
>
> _____
>
>YAHOO! GROUPS LINKS
>
>
>
>* Visit your group "firebird-support
><http://groups.yahoo.com/group/firebird-support> " on the web.
>
>
>* To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
><mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
>
>* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
><http://docs.yahoo.com/info/terms/> .
>
>
> _____
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>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
>
>
>
>