BUG: ALTER TABLE Can Add Duplicate Relations to DBC (189915)

SYMPTOMS

If you run the ALTER TABLE ... ADD FOREIGN KEY command, it will add a relation to the database that contains it. If you run the equivalent ALTER TABLE ... ADD FOREIGN KEY command again, it will add a duplicate relation to the database. This can cause duplicate stored procedures to be created with the Referential Integrity Builder, and these can cause duplication of functionality when the triggers are called.

MORE INFORMATION

Steps to Reproduce Behavior

Run the following code from a program (.prg) file:

      #DEFINE DBCNAME    'testdb'
      #DEFINE TABNAME    'testtab'
      #DEFINE TABCOUNT      2

      LOCAL lmRel1, lmRel2

      CLEAR
      CLOSE DATABASE ALL
      DELETE FILE testdb.DBC
      DELETE FILE testdb.dc?

      CREATE DATABASE DBCNAME

      FOR lni = 1 TO TABCOUNT
         DELETE FILE (TABNAME+ALLTR(STR(lni)))+'.dbf'
         DELETE FILE (TABNAME+ALLTR(STR(lni)))+'.cdx'
         CREATE TABLE (TABNAME+ALLTR(STR(lni))) (F1 C(10) NOT NULL ;
            PRIMARY KEY, ;
            F2 C(10) NOT NULL)
      ENDFOR

      ALTER TABLE (TABNAME+ALLTR(STR(lni-2))) ADD FOREIGN KEY TAG ;
         F1 REFERENCES (TABNAME+ALLTR(STR(lni-1))) TAG F1
      CLOSE DATABASE ALL
      USE DBCNAME+'.dbc'
      BROWSE FOR objecttype = 'Relation' TITLE "1st relation added" ;
         TIMEOUT 2
      USE
      OPEN DATABASE DBCNAME
      ALTER TABLE (TABNAME+ALLTR(STR(lni-2))) ADD FOREIGN KEY TAG ;
         F1 REFERENCES (TABNAME+ALLTR(STR(lni-1))) TAG F1
      CLOSE DATABASE ALL
      USE DBCNAME+'.dbc'
      BROWSE FOR objecttype = 'Relation' TITLE "2nd relation added" ;
         TIMEOUT 2
      LOCATE FOR objecttype = 'Relation'
      lmRel1 = property
      CONTINUE
      IF !EOF()
         lmRel2 = property
         ?lmRel1 = lmRel2, "The two relation property fields are equal"
      ELSE
         ?'2nd relation not found'
      ENDIF
      USE

Watch the two browse windows appear.

The first browse displays one relation after the first ALTER TABLE command. The second browse displays two relations after the second ALTER TABLE command. The Property fields of the two relations are equal. This indicates that the same relation has been added twice.

NOTES:

A DBC with multiple identical relation objects causes the RI Builder to display the relationship twice and causes the builder to generate two blocks of RI code, each of which is run every time the trapped event is triggered.

There will also be multiple rows for the relationship being displayed in the RI Builder's grid, and you could change the trigger settings for one but not the other(s).

BUG: ALTER TABLE Can Add Duplicate Relations to DBC (189915)

SYMPTOMS

STATUS

MORE INFORMATION

Steps to Reproduce Behavior