Checks for outstanding referential integrity violations before a commit.

Syntax

sa_check_commit( 
tname, 
keyname 
)

Arguments

Remarks

If the database option wait_for_commit is On, or if a foreign key is defined using CHECK ON COMMIT in the CREATE TABLE statement, you can update the database in such a way as to violate referential integrity, as long as these violations are resolved before the changes are committed.

You can use the sa_check_commit system procedure to check whether there are any outstanding referential integrity violations before attempting to commit your changes.

The returned parameters indicate the name of a table containing a row that is currently violating referential integrity, and the name of the corresponding foreign key index.

Permissions

None

Side effects

None

See also

• wait_for_commit option [database]
• CREATE TABLE statement

Example

The following set of commands can be executed from Interactive SQL. They delete rows from the Departments table in the sample database, in such a way as to violate referential integrity. The call to the sa_check_commit system procedure checks which tables and keys have outstanding violations, and the rollback cancels the change:

SET TEMPORARY OPTION wait_for_commit='On'
go
DELETE FROM Departments
go
CREATE VARIABLE tname VARCHAR( 128 );
CREATE VARIABLE keyname VARCHAR( 128 )
go
CALL sa_check_commit( tname, keyname )
go
SELECT tname, keyname
go
ROLLBACK
go