473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

UNIQUE INDEX bug when Trailling blanks in VARCHAR column

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
2 4472
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Westcoast Sheri | last post by:
Which will be a faster lookup of an item by, "color" in the following mySQL tables: "unique key," "primary key," or just plain "key"?? CREATE TABLE myTable ( number int(11) NOT NULL default '0',...
5
by: Laphan | last post by:
Hi All I know you can set a row to be unique, but I want expand on this in that I want the DB schema to only disallow an entry if the row isn't unique across 3 fields. Is it possible? My DDL...
2
by: Laphan | last post by:
Having re-jigged the UNIQUE to the proper syntax it does do what I want - wahey!! Just to finish this off here is my final table DDL: CREATE TABLE `WEBSTRINGS` ( `STRINGID` INT NOT NULL...
2
by: reneeccwest | last post by:
Hello, I plan to create a table with 3 unique keys. Combination of three fields has to be unique for each row in a table that are vendor ID (char 8), vendor name (char 40), and vendor...
6
by: Bill | last post by:
In an effort to improve the speed of queries against my main table, I'll be indexing a column whose data type is varchar(50). Would I be better off (better performance) if I changed the column's...
2
by: Benjamin Smith | last post by:
I have two tables like following: create table attendancereport ( id serial unique not null, staff_id integer not null references staff(id), schoolyear varchar not null references...
6
by: Joolz | last post by:
Hi everyone, When importing a bunch of data (> 85000 rows) I get an error I can't explain. The table into which I'm importing has a unique clause on (code, bedrijf). The rows in the source-table...
0
by: Rajesh Kumar Joshi | last post by:
I have a table with only 1 column as Table : profileTable //-------------- profile XML; //-------------- A sample content of XML file is <sampleprofile name="joe" version="A123">...
6
by: Alvin SIU | last post by:
Hi all, I have a table in Db2 v8 like this: Team Name Role ------ -------- --------------------- A Superman Leader A Batman Member A WonderWoman Member B ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.