By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,306 Members | 1,621 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,306 IT Pros & Developers. It's quick & easy.

UNIQUE INDEX bug when Trailling blanks in VARCHAR column

P: n/a
DB2 8.1
-------

db2 => create table test (COL1 VARCHAR(10))
db2 => insert into test values ('A')
db2 => insert into test values ('A ')
db2 => insert into test values ('B')
db2 => insert into test values ('B ')
db2 => insert into test values ('C')
db2 => select distinct COL1, LENGTH(COL1) from test

COL1 2
---------- -----------
A 1
B 1
C 1
A 2
B 2

5 record(s) selected.

db2 => CREATE UNIQUE INDEX TEST_IDX1 ON TEST(COL1)
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 rows
which are duplicates with respect to the values of the identified columns.
SQLSTATE=23515
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You cannot create a unique index on that column because the values are
not uniqe. This also means that the column cannot be a primary key.

However, you can define a simple index for the column.
CREATE INDEX TEST_IDX1 ON TEST(COL1)

Hope this helps

Christos Kalantzis


Laurent wrote:
DB2 8.1
-------

db2 => create table test (COL1 VARCHAR(10))
db2 => insert into test values ('A')
db2 => insert into test values ('A ')
db2 => insert into test values ('B')
db2 => insert into test values ('B ')
db2 => insert into test values ('C')
db2 => select distinct COL1, LENGTH(COL1) from test

COL1 2
---------- -----------
A 1
B 1
C 1
A 2
B 2

5 record(s) selected.

db2 => CREATE UNIQUE INDEX TEST_IDX1 ON TEST(COL1)
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 rows
which are duplicates with respect to the values of the identified columns.
SQLSTATE=23515

Nov 12 '05 #2

P: n/a
From the SQL Reference (String comparison):
"When comparing character strings of unequal lengths and the collating
sequence specified is not of the UCA (Unicode Collation Algorithm) type,
the comparison is made using a logical copy of the shorter string, which
is padded on the right with single-byte blanks sufficient to extend its
length to that of the longer string. This logical extension is done for
all character strings, including those tagged as FOR BIT DATA."

To the best of my knowledge this is SQL Standard
Cheers
Serge
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.