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.
CREATE TABLE t1( c1 int, c2 int, primary key(c1), CHECK( c1 < c2 ) );
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:
In 16.0, check constraint violations are detected first, resulting in a different outcome:
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.