VARCHAR, Trailing Spaces, and Unique Constraint

Another look at VARCHAR. This time, the implication of the 'important-ness' of trailing space(s) and constraint.

Let's start from a table with 3 rows:

db2 => select c1, length(c1) from smallt ;

C1   2
---- -----------
B              1
B              2
B              3

  3 record(s) selected.


As you can see, these three rows are 'unique' in the sense that they have different length. In Oracle, they are three distinct rows with all the 'expected' properties.

Can we create a unique index on it?

Let's try:


db2 => create unique index uqi on smallt (c1 );
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0603N  A unique index cannot be created because the table contains data
that would result in duplicate index entries.  SQLSTATE=23515


NOPE!

Another example of the 'useless' trailing spaces in VARCHAR.