 |
» |
|
|
|
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. Note that a template could employ either a BULK SELECT or a BULK FETCH statement. 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:
BULK SELECT UniqueColumn
INTO :TemplateArray, :StartIndex, :NumberOfRows
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:
BULK SELECT UniqueColumn1, UniqueColumn2, ..., UniqueColumnn
INTO :TemplateArray, :StartIndex, :NumberOfRows
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:
BULK SELECT ForeignKeyColumn
INTO :TemplateArray, :StartIndex, :NumberOfRows
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:
BULK SELECT ForeignKeyColumn1, ForeignKeyColumn2, ..., ForeignKeyColumnn
INTO :TemplateArray, :StartIndex, :NumberOfRows
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.
|
|
