PRB: Using Views with Non-Unique Key Fields (175933)

MORE INFORMATION

Steps to Reproduce Behavior

Run the following code:

   *Begin code
   SET SAFETY OFF
   SET EXCLUSIVE ON
   CLOSE DATA ALL
   CREATE DATABASE viewdemo
   CREATE TABLE sourcea (myfirst c(10),mysecond N(1,0))
   *Insert records
   INSERT INTO sourcea (myfirst,mysecond) VALUES ("Apple",1)
   INSERT INTO sourcea (myfirst,mysecond) VALUES ("Apple",2)
   INSERT INTO sourcea (myfirst,mysecond) VALUES ("Apple",3)
   INSERT INTO sourcea (myfirst,mysecond) VALUES ("Apple",4)
   CREATE SQL VIEW demoa ;
      AS SELECT * FROM sourcea ;
      ORDER BY myfirst                           && Create View
   =DBSETPROP('DEMOA','View','SendUpdates',.T.)  && Enable Updates
   =DBSETPROP('DEMOA','View','UpdateType',1)     && Use SQL Updates
   =DBSETPROP('DEMOA','View','WhereType',3)      && Modify key / changed
   =DBSETPROP('DEMOA','View','UseMemoSize',255)  && Memo size = 255
   =DBSETPROP('DEMOA','View','MaxRecords',-1)    && All Records
   =DBSETPROP('DEMOA','View','Tables','sourcea') && Source table
   =DBSETPROP('DEMOA','View','FetchMemo',.F.)    && Don't get memo
   =DBSETPROP('DEMOA','View','FetchSize',100)    && Fetch size=100
   =DBSETPROP('DEMOA.myfirst','Field','KeyField',.T.) && Key Field
   =DBSETPROP('DEMOA.myfirst','Field','Updatable',.T.) && Updateable
   =DBSETPROP('DEMOA.myfirst','Field','UpdateName','sourcea.myfirst')
   =DBSETPROP('DEMOA.myfirst','Field','DataType',"C(10)")
   =DBSETPROP('DEMOA.mysecond','Field','KeyField',.F.) && Not a key field
   =DBSETPROP('DEMOA.mysecond','Field','Updatable',.T.)&& Updateable
   =DBSETPROP('DEMOA.mysecond','Field','UpdateName','sourcea.mysecond')
   =DBSETPROP('DEMOA.mysecond','Field','DataType',"N(1,0)")
   SELECT sourcea
   BROWSE TITLE "BASE TABLE FOR SQL VIEW" NODELETE NOEDIT NOAPPEND ;
      TIMEOUT(5)
   USE demoa
   GO TOP
   REPLACE myfirst WITH "Orange"   && Replace in one record
   =TABLEUPDATE(.T.,.T.,'DEMOA')
   SELECT sourcea
   GO TOP
   BROWSE TITLE "BASE TABLE AFTER A SINGLE REPLACE IN A VIEW WITH" + ;
      " Non-unique keyfield"  NODELETE NOEDIT NOAPPEND TIMEOUT(5)
   SELECT demoa
   =REQUERY("DEMOA")
   GO TOP
   DELETE                          && Delete one record
   =TABLEUPDATE(.T.,.T.,'DEMOA')
   SELECT sourcea
   GO TOP
   BROWSE TITLE "BASE TABLE AFTER DELETING ONE RECORD IN A VIEW WITH" + ;
      " Non-unique keyfield"  NODELETE NOEDIT NOAPPEND TIMEOUT(5)
   * Now create unique key fields in base table
   DO WHILE !EOF()
      IF DELETED()
         RECALL                    && Recall deleted records
      ENDIF
      REPLACE myfirst WITH "Apple "+ALLTRIM(STR(RECNO())) && Set Unique Key
      SKIP
   ENDDO
   GO TOP
   BROWSE TITLE "BASE TABLE TABLE FOR SQL VIEW" NODELETE NOEDIT ;
      NOAPPEND TIMEOUT(5)
   SELECT demoa
   =REQUERY("demoa")
   GO TOP
   REPLACE myfirst WITH "Orange"   && Replace in one record
   =TABLEUPDATE(.T.,.T.,'DEMOA')
   SELECT sourcea
   BROWSE TITLE "BASE TABLE AFTER A SINGLE REPLACE IN A VIEW WITH" + ;
      " Unique keyfield"  NODELETE NOEDIT NOAPPEND TIMEOUT(5)
   SELECT demoa
   =REQUERY("DEMOA")
   GO TOP
   LOCA FOR myfirst="Orange"
   DELETE                          && Delete one record
   =TABLEUPDATE(.T.,.T.,'DEMOA')
   SELECT sourcea
   GO TOP
   BROWSE TITLE "BASE TABLE AFTER DELETING ONE RECORD IN VIEW WITH" + ;
      " Unique keyfield"  NODELETE NOEDIT NOAPPEND TIMEOUT(5)
   RETURN
   *End code
				

With a non-unique key included in the SQL View, the first browse window displays four records with "Apple" in the Myfirst field. The second browse window displays four records with "Orange" in the Myfirst field. The third browse window displays four deleted records with "Orange" in the Myfirst field. This behavior is not observed when the KeyField of the SQL View is unique.