DB002112 on CREATE CONSTRAINT is a WARNING
Article ID: 122895
Updated On:
Products
IDMS
IDMS - Database
Issue/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.
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.
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.
Resolution
This simple syntax describes the original behaviour behind referential constraint enforcement:
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:
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.
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
Feedback
Yes
No
Powered by
