All,
I learnt very early on in my Informix career that the maximum length of a varchar column is limited to 254 characters, instead of 255, if the column is indexed. This restriction is documented here:
https://www.ibm.com/docs/en/informix-servers/14.10?topic=types-varcharmr-data-typeToday I have come across a column which is varchar(255) and used in multiple indices.
Could anyone shed any light on any of the following aspects:
* why this restriction exists (or existed).
* whether it still exists.
* what bad things might happen if it is violated.
* why the db engine does not enforce it.
This little dialogue shows that, on the face of it, it is not a problem:
drop table if exists t1;
Table dropped.
create table t1 (
col1 varchar(255)
);
Table created.
insert into t1 (col1) values ('012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234');
1 row(s) inserted.
select col1, len(col1) from t1;
col1 012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
012345678901234567890123456789012345678901234567890123456789012345678901234
(expression) 255
1 row(s) retrieved.
create unique index ix_t1 on t1(col1);
Index created.
select * from t1 where col1='012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234';
col1 01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
8901234567890123456789012345678901234567890123456789012345678901234
1 row(s) retrieved.
------------------------------
Benjamin Thompson
------------------------------
#Informix