Database get corrupted when column is renamed for which check constraint was defined inside create table statement.

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.
CREATE CACHED TABLE PUBLIC.ACCOUNTS(SHOW_USERNAME_IN_REVIEW_CATEGORY CHAR(1) 
DEFAULT 'N' NOT NULL CHECK (SHOW_USERNAME_IN_REVIEW_CATEGORY IN('Y', 'N'))
)
2.
ALTER TABLE ACCOUNTS ALTER SHOW_USERNAME_IN_REVIEW_CATEGORY RENAME TO 
SHOW_USER_IN_REVIEW;
3.
Database get corrupted: disconnect, connect
Column "SHOW_USERNAME_IN_REVIEW_CATEGORY" not found; SQL statement:
CREATE CACHED TABLE PUBLIC.ACCOUNTS(
    SHOW_USER_IN_REVIEW CHAR(1) DEFAULT 'N' NOT NULL CHECK (SHOW_USERNAME_IN_REVIEW_CATEGORY IN('Y', 'N'))
) [42122-172] 42S22/42122

Before disconnect:

SELECT SQL FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'ACCOUNTS'

CREATE CACHED TABLE PUBLIC.ACCOUNTS(
    SHOW_USER_IN_REVIEW CHAR(1) DEFAULT 'N' NOT NULL CHECK (SHOW_USERNAME_IN_REVIEW_CATEGORY IN('Y', 'N'))
)

shows that the constraint inside table definition was not renamed and still 
references the original column

What is the expected output? What do you see instead?

database is not corrupted. Check constraint is renamed too. 

What version of the product are you using? On what operating system, file
system, and virtual machine?

Version 1.3.172 (2013-05-25)

Do you know a workaround?
Yes

Workaround #1:

when the constraint was defined not inside the create statement but with alter 
table add constraint statement:

CREATE CACHED TABLE PUBLIC.ACCOUNTS(
    SHOW_USERNAME_IN_REVIEW_CATEGORY CHAR(1) DEFAULT 'N' NOT NULL);

ALTER TABLE ACCOUNTS ADD CONSTRAINT SHOW_USERNAME_IN_REVIEW_CATEGORY_CHECK 
CHECK (SHOW_USERNAME_IN_REVIEW_CATEGORY IN ('Y', 'N'))

SELECT SQL FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'ACCOUNTS':

CREATE CACHED TABLE PUBLIC.ACCOUNTS(
    SHOW_USERNAME_IN_REVIEW_CATEGORY CHAR(1) DEFAULT 'N' NOT NULL
)

SELECT CHECK_EXPRESSION  FROM INFORMATION_SCHEMA.CONSTRAINTS :

(SHOW_USERNAME_IN_REVIEW_CATEGORY IN('Y', 'N'))

ALTER TABLE ACCOUNTS ALTER SHOW_USERNAME_IN_REVIEW_CATEGORY RENAME TO 
SHOW_USER_IN_REVIEW;

SELECT CHECK_EXPRESSION  FROM INFORMATION_SCHEMA.CONSTRAINTS 

(SHOW_USER_IN_REVIEW IN('Y', 'N'))

What is your use case, meaning why do you need this feature?
This is the way we define the constraints in Gerrit: inside create table 
statement.
So we are not able to rename the column at all.

Workaround #2: doesn't work, because we don't have the name of the constraint, 
it is generated automatically:

Drop the constraint
Rename Column
Add constraint

-- we did it inside the create table statement
--ALTER TABLE ACCOUNTS ADD CONSTRAINT SHOW_USERNAME_IN_REVIEW_CHECK CHECK 
(SHOW_USERNAME_IN_REVIEW_CATEGORY IN ('Y', 'N'))
ALTER TABLE ACCOUNTS DROP CONSTRAINT SHOW_USERNAME_IN_REVIEW_CHECK
ALTER TABLE ACCOUNTS ALTER COLUMN SHOW_USERNAME_IN_REVIEW_CATEGORY RENAME TO 
SHOW_USER_IN_REVIEW
ALTER TABLE ACCOUNTS ADD CONSTRAINT SHOW_USER_IN_REVIEW_CHECK CHECK 
(SHOW_USER_IN_REVIEW IN('Y', 'N'))

Workaround #3: (didn't test it):

Add another column with the same type
Update table, set all value from old column to new column
Drop old column
Original issue reported on code.google.com by David.Os...@gmail.com on 12 Jul 2013 at 3:19
hamidhtc / h2database

Database get corrupted when column is renamed for which check constraint was defined inside create table statement. #485
