Thanks, Stephen, but there is _definitely_ no duplicate row. I think that fact is confirmed by R:Base being able to create the PK with 3 ASC columns.
However, since my original post, I have investigated further - even to the extent of going to the User Manual! I looked at the entry for "Databases - Using Constraints" and found that it does positively state "click the Asc button" - no mention of DEScending. I looked next at the entry for "Applying Indexes" and that states "Click the Ascending or Descending button to specify the sorting order of this index". When a constraint is defined in RBDefine you are asked to choose Asc/Desc _before_ defining the type of constraint or index. Because the choice of Descending is offered it's not too surprising that it got chosen but, perhaps, there should be some sort of warning message when a constraint is later selected to tell the user that only Ascending can be applied and the previous selection will be changed. I've now changed my constraint - it's not the end of the world. Regards, Alastair. ----- Original Message ----- From: "Stephen Markson" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Thursday, June 12, 2003 2:11 PM Subject: [RBASE-L] - RE: Can a multi-column Primary Key be sorted? > 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] > > ---------------------------------- >
