HP 3000 Manuals HP 3000 Manuals
Locating Constraint Errors
When your transaction defers constraint checking, you can minimize the
possibility of rollback due to constraint errors by setting constraint
checking to IMMEDIATE just before the COMMIT WORK statement is executed.
Then check sqlcode for constraint errors. If errors were encountered,
either prompt the user to make corrections or use the trouble shooting
templates below to locate the errors. Once all errors have been
corrected, issue a COMMIT WORK statement. The "Coding with Deferred
Constraint Error Checking" section provides an additional example.
Template for Single Column Unique Constraint Errors
This template returns the values in rows where a unique value in a single
column unique constraint or unique index is duplicated:
SELECT UniqueColumn
FROM UniqueTable
GROUP BY UniqueColumn
HAVING COUNT (UniqueColumn) > 1
Template for Multiple Column Unique Constraint Errors
This template returns the values in rows where unique values are
duplicated in a multiple column unique constraint or unique index having
n columns:
SELECT UniqueColumn1, UniqueColumn2, ..., UniqueColumnn
FROM UniqueTable
GROUP BY UniqueColumn1, UniqueColumn2, ..., UniqueColumnn
HAVING COUNT (UniqueColumn1) > 1
AND COUNT (UniqueColumn2) > 1
:
AND COUNT (UniqueColumnn) > 1
Template for Single Column Referential Constraint Errors
This template returns the values in rows where the referencing value in a
single referencing column matches no referenced value:
SELECT ForeignKeyColumn
FROM ForeignKeyTable
WHERE ForeignKeyColumn IS NOT NULL
AND NOT EXISTS (SELECT *
FROM PrimaryKeyTable
WHERE ForeignKeyColumn = PrimaryKeyColumn)
Template for Multiple Column Referential Constraint Errors
This template returns the values in rows where the referencing values in
a multiple column referencial constraint with n columns match no
referenced values:
SELECT ForeignKeyColumn1, ForeignKeyColumn2, ..., ForeignKeyColumnn
FROM ForeignKeyTable
WHERE ForeignKeyColumn1 IS NOT NULL
AND ForeignKeyColumn2 IS NOT NULL
:
AND ForeignKeyColumnn IS NOT NULL
AND NOT EXISTS (SELECT *
FROM PrimaryKeyTable
WHERE ForeignKeyColumn1 = PrimaryKeyColumn1
AND ForeignKeyColumn2 = PrimaryKeyColumn2
:
AND ForeignKeyColumnn = PrimaryKeyColumnn
Coding with Deferred Constraint Error Checking
Suppose the user wants to update information in the Clubs table and in
the Members table of RecDB. The Club column in the Members table
references the ClubName column in the Clubs table, and the ClubContact
column in the Clubs table references the MemberName column in the Members
table. It is not possible to update both of these tables in the same
instant, and a referential constraint error could occur if one table is
modified and the other table is still unchanged. In order to resolve
these circular referential constraints within the same transaction, you
can defer constraint error checking until the end of the transaction at
which point all constraints are resolved, as in the following example:
(Error checking is set to statement level, the default.)
Execute subroutines to display and prompt for information needed in the Clubs table
and the Members table.
Place user entered data in appropriate host variables.
BEGIN WORK
At this point you want to update the Clubs table. However, ClubContact in the Clubs
table references MemberName in the Members table, and the Members table does not yet
have the appropriate primary key value inserted.
Defer referential error checking to the transaction level so that all constraints
in the transaction can be resolved before constraint errors are checked.
SET REFERENTIAL CONSTRAINTS DEFERRED
UPDATE RecDB.Clubs
SET ClubName = :NewClubName :ClubNameInd,
ClubPhone = :ClubPhone,
Activity = :Activity,
ClubContact = :ClubContact
WHERE ClubName = :ClubName
These indented statements are shown to illustrate
the warning issued when constraint checking is set
to a state at which it already exists and to show
what constraint errors would stop statement execution
if constraint checking had not been deferred.
SET REFERENTIAL CONSTRAINTS DEFERRED
A warning is issued, since constraints are already deferred.
REFERENTIAL constraints already set to DEFERRED. (DBWARN 2066)
A referential constraint error occurs at this point. If you
set constraints to IMMEDIATE, an error is issued saying that
there are one or more referential constraint errors, but
constraints stay deferred because the SET CONSTRAINTS
IMMEDIATE statement fails when outstanding constraint
errors exist.
SET REFERENTIAL CONSTRAINTS IMMEDIATE
FOREIGN KEY constraint violated. (DBERR 2293)
The sqlcode field of the sqlca equals -2293 because no primary
key exists for the foreign key ClubContact. Constraint
checking remains deferred.
Resolve the unsatisfied constraints by inserting the necessary primary
keys in the Members table.
INSERT INTO RecDB.Members
VALUES MemberName = :MemberName,
Club = :Club,
MemberPhone = :MemberPhone :MemberPhoneInd
Set constraint error checking to IMMEDIATE. If the SET CONSTRAINTS IMMEDIATE
statement succeeds, constraints are set to IMMEDIATE. If the SET CONSTRAINTS
IMMEDIATE statement fails because of constraint errors, constraints remain
deferred. No rollback occurs.
SET REFERENTIAL CONSTRAINTS IMMEDIATE
Check the sqlcode field of the sqlca. If constraint errors exist, you could code
statements that locate them, (See the templates in the previous section.) or you could
prompt the user for input to correct the errors.
When all constraint errors are resolved, commit the transaction.
COMMIT WORK
If sqlcode is negative, the transaction is rolled back. Inform the user.
For example, if sqlcode equals -2293, indicating no primary key match, display the
error message and prompt the user to indicate whether or not to insert a new
MemberName/Club primary key in the Members table or a new ClubName primary
key in the Clubs table or to exit the transaction. Execute the appropriate subroutine.
Else, if sqlcode = 0, tell the user the transaction was successfully completed, and
prompt for additional information for the Clubs and Members tables or a return to
the main menu display.