I have upgraded my spring boot version from 1.5.x to 2.0.0 and updated h2 DB version to 1.4.200. In the process, h2 is throwing error with my alter column command.
Actual alter command
--ALTER TABLE log_activity
--CHANGE COLUMN name name VARCHAR(255) NOT NULL,
--CHANGE COLUMN address address TEXT NOT NULL,
--CHANGE COLUMN status status TEXT NOT NULL;
Modified alter command (ref: https://www.h2database.com/html/commands.html#alter_table_alter_column)
ALTER TABLE log_activity
ALTER COLUMN name SET NOT NULL,
ALTER COLUMN address SET NOT NULL,
ALTER COLUMN status SET NOT NULL;
Still I am facing syntax issues with the above commands.
SQL State : 42000
Error Code : 42000
Message : Syntax error in SQL statement "ALTER TABLE log_activity
ALTER COLUMN name SET NOT NULL,[*]
ALTER COLUMN address SET NOT NULL,
ALTER COLUMN status SET NOT NULL"; SQL statement:
ALTER TABLE log_activity
ALTER COLUMN name SET NOT NULL,
ALTER COLUMN address SET NOT NULL,
ALTER COLUMN status SET NOT NULL [42000-200]
.
.
.
Line : 6
Statement : ALTER TABLE log_activity
ALTER COLUMN name SET NOT NULL,
ALTER COLUMN address SET NOT NULL,
ALTER COLUMN status SET NOT NULL
I am could see there is [*] gets appended after the ALTER COLUMN name SET NOT NULL,
I am kinda stuck with this issue since two days.
Can you please tell me where I am going wrong.
Neither CHANGE COLUMN nor standard ALTER COLUMN can be used to alter multiple columns at once in H2, you need to write three separate commands, for example, with ALTER COLUMN:
ALTER TABLE log_activity ALTER COLUMN name SET NOT NULL;
ALTER TABLE log_activity ALTER COLUMN address SET NOT NULL;
ALTER TABLE log_activity ALTER COLUMN status SET NOT NULL;
The SQL Standard also doesn't have multi-column DDL operations, only some DBMS support them with various vendor-specific syntaxes.
Please note that 1.4.200 is an old unsupported version of H2 Database.
ALTER TABLE … CHANGE COLUMN is accepted by newer versions of H2 only in MySQL and MariaDB compatibility modes, if you want to use CHANGE COLUMN in H2 2.*.*, you need to enable one of these modes first:
https://h2database.com/html/features.html#compatibility