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

Thanks for the test case. Fixed in SVN.