Search code examples
sqldatabaseprimary-keyhsqldb

hsqldb data exception: string data, right truncation in statement

Email table was created using following command and currently it has around ~15000 records.

Create table emails (id INTEGER, accountid INTEGER, subject varchar(4000), 
sender varchar(100), sentDate TIMESTAMP, status varchar(10))

Now I am trying to add primary key on this table. For this purpose I am executing 

ALTER TABLE emails ADD PRIMARY KEY (ID)

But this action is resulting into 

data exception: string data, right truncation in statement [ALTER TABLE emails ADD 
PRIMARY KEY (ID)]

What could be the reason?

Solution

  • The reason is in some rows one of the VARCHAR columns contains data that is longer than the declared size of the column.

    You need to use a SELECT to find the longest strings and change the table definition with longer column definitions where necessary. For example:

    SELECT MAX (CHAR_LENGTH(status)) FROM emails

ALTER TABLE emails ALTER COLUMN status SET DATA TYPE VARCHAR(20)