DB002112 on CREATE CONSTRAINT is a WARNING

Document ID : KB000122895
Last Modified Date : 12/12/2018
Show Technical Document Details
Introduction:
This article describes the history and behaviour behind the DB002112 message issued when an unlinked CONSTRAINT is created without a supporting foreign key index.
Background:
When creating an unlinked referential CONSTRAINT, originally, CA-IDMS required that there be an index (or calc key) on the foreign key columns on the referencing table.
The documentation indicates that that requirement is still there.
However, a change was introduced during the r16.0 timeframe which removed this requirement.
This was implemented with APAR QO91220.
Environment:
CA IDMS, all supported releases.
Instructions:
This simple syntax describes the original behaviour behind referential constraint enforcement:
CREATE TABLE RCTEST.OWNER                                          
  ( OWNERKEY                         UNSIGNED NUMERIC(4) NOT NULL  
  );                                                               
*+ Status = 0        SQLSTATE = 00000                              
CREATE UNIQUE INDEX IX_OWNER                                       
    ON RCTEST.OWNER                                                
        ( OWNERKEY ASC );                                          
*+ Status = 0        SQLSTATE = 00000                              
CREATE TABLE RCTEST.MEMBER                                         
  ( MEMBERKEY                         UNSIGNED NUMERIC(4) NOT NULL,
    OWNERKEY                          UNSIGNED NUMERIC(4) NOT NULL 
  );                                                               
*+ Status = 0        SQLSTATE = 00000                              
CREATE UNIQUE INDEX IX_MEMBER                                      
    ON RCTEST.MEMBER                                               
        ( MEMBERKEY ASC );                                         
*+ Status = 0        SQLSTATE = 00000                              
CREATE INDEX IX_MEMBER_OWNER                                       
    ON RCTEST.MEMBER                                               
        ( OWNERKEY ASC );                                          
*+ Status = 0        SQLSTATE = 00000                              
CREATE CONSTRAINT OWNER_MEMBER                                     
    RCTEST.MEMBER                                                  
        ( OWNERKEY )                                               
    REFERENCES RCTEST.OWNER                                        
        ( OWNERKEY )                                               
    UNLINKED;                                                      
*+ Status = 0        SQLSTATE = 00000                              
INSERT INTO RCTEST.OWNER VALUES (1);                               
*+ Status = 0        SQLSTATE = 00000                              
*+ 1 row processed                                                 
INSERT INTO RCTEST.OWNER VALUES (2);                               
*+ Status = 0        SQLSTATE = 00000                              
*+ 1 row processed                                                 
INSERT INTO RCTEST.MEMBER VALUES (10,2);                           
*+ Status = 0        SQLSTATE = 00000                              
*+ 1 row processed                                                 
DELETE FROM RCTEST.OWNER WHERE OWNERKEY=1;                         
*+ Status = 0        SQLSTATE = 00000                              
*+ 1 row processed                                                 
DELETE FROM RCTEST.OWNER WHERE OWNERKEY=1;                         
*+ Status = 100      SQLSTATE = 02000                              
*+ No qualifying rows found                                   
DELETE FROM RCTEST.OWNER WHERE OWNERKEY=2;                    
*+ Status = -4       SQLSTATE = 23000        Messages follow: 
*+ DB001059 T94 C-4M321: Referential constraint violation,    
*+                         Table:OWNER Constraint:OWNER_MEMBER
*+                         Matching foreign keys extant
Note that a supporting foreign key index, IX_MEMBER_OWNER, was created for the constraint.
This is necessary so that if an attempt is made to delete a row of the referenced table (OWNER in this case), CA-IDMS can efficiently determine whether or not any related rows of the referencing table (MEMBER) exist.
In the data added, there were no such rows for OWNERKEY=1 so the DELETE was allowed.
However there were referencing rows for OWNERKEY=2, so the DELETE was disallowed with the DB001059 message.

The change introduced by QO91220 removed the requirement for the supporting index.
However, this of course means that CA-IDMS can no longer efficiently check for the existence of related referencing rows, so the trade-off is that a DELETE of *any* rows of the referenced table is disallowed.
Note the following syntax - the only difference is that no supporting index was created:
CREATE TABLE RCTEST.OWNER                                           
  ( OWNERKEY                         UNSIGNED NUMERIC(4) NOT NULL   
  );                                                                
*+ Status = 0        SQLSTATE = 00000                               
CREATE UNIQUE INDEX IX_OWNER                                        
    ON RCTEST.OWNER                                                 
        ( OWNERKEY ASC );                                           
*+ Status = 0        SQLSTATE = 00000                               
CREATE TABLE RCTEST.MEMBER                                          
  ( MEMBERKEY                         UNSIGNED NUMERIC(4) NOT NULL, 
    OWNERKEY                          UNSIGNED NUMERIC(4) NOT NULL  
  );                                                                
*+ Status = 0        SQLSTATE = 00000                               
CREATE UNIQUE INDEX IX_MEMBER                                       
    ON RCTEST.MEMBER                                                
        ( MEMBERKEY ASC );                                          
*+ Status = 0        SQLSTATE = 00000                               
CREATE CONSTRAINT OWNER_MEMBER                                      
    RCTEST.MEMBER                                                   
        ( OWNERKEY )                                                
    REFERENCES RCTEST.OWNER                                         
        ( OWNERKEY )                                                
    UNLINKED;                                                       
*+ Status = 1        SQLSTATE = 01000        Messages follow:       
*+ DB002112 T105 C1M6004: No index defined on foreign key           
INSERT INTO RCTEST.OWNER VALUES (1);                                
*+ Status = 0        SQLSTATE = 00000                               
*+ 1 row processed                                                  
INSERT INTO RCTEST.OWNER VALUES (2);                                
*+ Status = 0        SQLSTATE = 00000                               
*+ 1 row processed                                                  
INSERT INTO RCTEST.MEMBER VALUES (10,2);                            
*+ Status = 0        SQLSTATE = 00000                               
*+ 1 row processed                                                  
DELETE FROM RCTEST.OWNER WHERE OWNERKEY=1;                          
*+ Status = -4       SQLSTATE = 60000        Messages follow:       
*+ DB001101 T105 C601M321: DELETE of table with unlinked constraint 
*+ OWNER_MEMBER and no foreign key index is not allowed             
DELETE FROM RCTEST.OWNER WHERE OWNERKEY=1;                          
*+ Status = -4       SQLSTATE = 60000        Messages follow:       
*+ DB001101 T105 C601M321: DELETE of table with unlinked constraint 
*+ OWNER_MEMBER and no foreign key index is not allowed
Note there are two differences in the output here.
First, the CREATE CONSTRAINT returns the DB002112 message.
This is a warning only - the constraint is still created.
The second difference is that both attempts to delete a row from the referenced table returned DB001101, whether there were existing referencing rows or not.

The logic behind the introduction of this behaviour was that often the referenced table represents very static data that is rarely if ever changed or deleted.
One good example is a table of post codes.
It seemed an excessive overhead to maintain such supporting foreign key indexes given that they most likely will never be used.

 


 
Additional Information:
CREATE CONSTRAINT
QO91220