Primary key constraints are not verified after data type changes

Detail name	Value
Changelog Number	14802
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.11, Exasol 8.0.3
Resolution Date	2022-06-09

Description

Altering the data type of a column incorrectly succeeds if the following conditions are met

• the datatype of a column is altered
• the type conversion truncates the data causing duplicates
• the column is part of the primary key
  The expected behavior is that the statement that changes the data type fails and raises a constraint violation error.

Preparation

create or replace table t(c timestamp primary key);
insert into t values 
timestamp '2022-05-06 13:00',
timestamp '2022-05-06 12:00';

Example casting timestamp to date which changes the values of both rows to '2022-05-06'

alter table t modify column c date; -- this should raise a constraint violation exception
select * with invalid primary key (c) from t; -- returns two rows
insert into t values date '2022-01-01'; --raises a constraint violation exception

Workaround

Use

select * with invalid primary key

to verify the integrity of the primary key constraint after an ALTER operation.

Fix

In those scenarios the ALTER operation fails, raising a "constraint violation exception"