ANSI_UPDATE_CONSTRAINTS Option

Allowed Values

OFF, CURSORS, STRICT

Default

CURSORS

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

SAP Sybase IQ provides several extensions that allow updates that are not permitted by the ANSI SQL standard. These extensions provide powerful, efficient mechanisms for performing updates. However, in some cases, they cause behavior that is not intuitive. This behavior might produce anomalies such as lost updates if the user application is not designed to expect the behavior of these extensions.

ANSI_UPDATE_CONSTRAINTS controls whether updates are restricted to those permitted by the SQL92 standard.

If the option is set to STRICT, these updates are prevented:

If the option is set to CURSORS, these same restrictions are in place, but only for cursors. If a cursor is not opened with FOR UPDATE or FOR READ ONLY, the database server determines whether updates are permitted based on the SQL92 standard.

If ANSI_UPDATE_CONSTRAINTS is set to CURSORS or STRICT, cursors containing an ORDER BY clause default to FOR READ ONLY; otherwise, they continue to default to FOR UPDATE.

Example

This code has a different effect, depending on the setting of ANSI_UPDATE_CONSTRAINTS:

CREATE TABLE mmg (a CHAR(3));
CREATE TABLE mmg1 (b CHAR(3));

INSERT INTO mmg VALUES ('001');
INSERT INTO mmg VALUES ('002');
INSERT INTO mmg VALUES ('003')
INSERT INTO mmg1 VALUES ('003');
SELECT * FROM mmg;
SELECT * FROM mmg1;

Option 1: Set ANSI_UPDATE_CONSTRAINTS to STRICT:

SET OPTION public.Ansi_update_constraints = 'strict';
DELETE MMG FROM MMG1 WHERE A=B;

This results in an error indicating that the attempted update operation is not allowed.

Option 2: Set ANSI_UPDATE_CONSTRAINTS to CURSORS or OFF:

SET OPTION public.Ansi_update_constraints = 'CURSORS';  // or 'OFF'
DELETE mmg FROM mmg1 WHERE A=B;

In this case, the deletion should complete without the error.