ASA Database Administration Guide
  Database Options
    Alphabetical list of options

ANSI_UPDATE_CONSTRAINTS option [compatibility]

Function 

Controls the range of updates that are permitted.

Allowed values 

OFF, CURSORS, STRICT

Default 

CURSORS in new databases.

OFF in databases created before version 7.0.

See also 

UPDATE statement

Description 

Adaptive Server Anywhere provides several extensions that allow updates which 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 can produce anomalies such as lost updates if the user application is not designed to expect the behavior of these extensions.

The ANSI_UPDATE_CONSTRAINTS option controls whether updates are restricted to those permitted by the SQL/92 standard.

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

• Updates of cursors containing JOINS

• Updates of columns that appear in an ORDER BY clause

• The FROM clauses is not allowed in UPDATE statements

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 chooses updatability based on the SQL/92 standard. If the ANSI_UPDATE_CONSTRAINTS option is CURSORS or STRICT, cursors containing an ORDER BY clause default to FOR READ ONLY; otherwise, they continue to default to FOR UPDATE.