I have seen this before. With PACK, the duplicate row is kept, but the index doesn't know about it. In order to find the duplicate you need to remove the PK first. Then, find the duplicate by SELECT (PK columns) FROM table GROUP BY (PK columns) HAVING COUNT(*)>1.
Maybe with RELOAD, the duplicate row was NOT reloaded. When RELOAD encountered the duplicate row, it issued the error message and did not reload the duplicate row? I don't know. The sort order for indexes is maintained in the SYS_INDEXES table and can only be altered (as far as I know) from RBDefine. This information is used during the RELOAD and your PK should be defined the same way as it was before the reload. Regardless of the sort order of the columns in the PK, you should be able to have "header" before "footer" by specifying the sort order in your SELECT or other statement. Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForensicSystemsGroup.com 416 482 2140 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Alastair Burr > Sent: June 12, 2003 6:32 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Can a multi-column Primary Key be sorted? > > I have a table which, sometimes, when I reload the database I am told > that > it cannot have duplicate rows. Now I know that sometimes is impossible > but, > believe me, it happens. > > It happened again today so I looked at the table again and output a > backup > to a file and examined that. Nothing obviously wrong so I ran the file > to > replace the table (deleting the original first). It loaded and created > the > PK with no problem. > > I then look at the table in the designer and noticed that the 3 > columns in > the PK were sorted ASC whereas when I defined then two were ASC and > the > middle one was DESC. The designer allows this to be done. (The middle > column > simple specifies "header" or "footer" and I prefer "header" to come > before > "footer" - no other reason.) > > I then looked at the output file again and noticed that neither ASC > nor DESC > are assigned to the PK definition. I then checked R:Syntax and that > doesn't > appear to give any option to define the sort for a PK at all. > > I then deleted and tried to create any 3 columns with a DESC sort as > part of > a PK and some combinations would work and others would not accept it. > Now I > don't understand how there can be no duplicate rows if the 3 sorts are > ASC > and sometimes there are duplicates if one column is DESC. > > I then connected a backup copy of the same database and looked at the > definition for that table and the middle column is defined as DES and > the > other two as ASC. The backup was made just before I tried to reload > and got > the error message today. > > Oh, by the way, there are only 100 rows in the table and it is very > easy to > check for duplicates and there are not any. > > Should I be able to choose the sequence or has something changed > recently? > > Regards, > Alastair. > > ---------------------------------- > A D B Burr, > St. Albans, UK. > ---------------------------------- > [EMAIL PROTECTED] > ----------------------------------
