firebird
firebird copied to clipboard
Alter domain success but expression index fail then
CREATE DOMAIN DOMAIN_REGION VARCHAR(5);
CREATE TABLE REGION
(NAME DOMAIN_REGION );
CREATE INDEX IXAE_REGION__NAME ON REGION COMPUTED BY(UPPER(NAME));
commit;
INSERT INTO REGION(NAME) VALUES('12345');
INSERT INTO REGION(NAME) VALUES('abcde');
commit;
ALTER DOMAIN DOMAIN_REGION TYPE VARCHAR(20);
commit;
now try to update table to use extended length of the domain.
UPDATE REGION SET NAME = NAME || '67890';
Message: isc_dsql_execute2 failed
Engine Code : 335545153
Engine Message :
unknown ISC error 335545153
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 5, actual 10
If you drop index IXAE_REGION__NAME, then above update is working.
3 things to improve this:
- update index too if have dependency of domain. or
- raise an error about index dependency when altering domain. or
- improve error message and write there that reason of an error is
IXAE_REGION__NAME.
Now it is nightmare to find exect reason what happened, where this truncation occur, especially if db structure is not trivial as above sample ;-)