Constraint Violation Checks

The order in which the database server evaluates check constraints and unique constraints has changed in SAP Sybase IQ 16.0. This behavior change occurs only for LOAD or INSERT statements containing the clause IGNORE CONSTRAINT CHECK <x>, UNIQUE <y>.

Check constraints are now evaluated before unique constraints. This behavior change may lead to different load results compared to 15.4, depending on the constraints specified, and the data being loaded.

Consider this simple LOAD TABLE example comparing 15.4 constraint evaluation behavior with 16.0:

Create the table:

CREATE TABLE t1( c1 int, c2 int, primary key(c1), CHECK( c1 < c2 ) );

Load values:
```
LOAD TABLE t1 { 4,3 }, {4,5 } FROM ‘sample_data.dat’ IGNORE CONSTRAINT CHECK 0, UNIQUE 0
```
In 15.4, the database server detects unique constraint violations before check constraint violations:
- HG index detects a unique violation on { 4, 5 }, so row 2 is removed.
- This leaves row 1 which contains { 4, 3 }. This violates the check constraint, so row 1 is removed.
- The result is zero rows in the table.
In 16.0, check constraint violations are detected first, resulting in a different outcome:
- Row { 4,3 } is discarded due to the check constraint violation.
- HG index only sees one row so there is no unique constraint violation.
- The resulting row { 4, 5 } does not violate the check constraint.
- The result is one row in the table: { 4, 5 }.

A related behavior change affects column defaults in statements containing the clause IGNORE CONSTRAINT CHECK. In SAP Sybase IQ 16.0, column default violations are never ignored.