ALTER TABLE "table-name" MODIFY "columne" NOT NULL; fail with Unknown data type: "NOT";

[Sam Blume]

Version: h2-1.4.196

ALTER TABLE "table-name" MODIFY "columne-name" NOT NULL; fails with Unknown data type: "NOT";

- - -

I kowticed that h2 started to support the "ALTER TABLE - MODIFY"-SQL syntax (used in Oracle) since ca. Version: h2-1.4.19x.

In the older version h2-1.4.192 it failed when trying to alter to NULL (e.g: ALTER TABLE "table-name" MODIFY "columne-name" NULL).

This seames to have been fixed when using the latest version: h2-1.4.196.

But what still is failing is the "NOT NULL" case: "ALTER TABLE "table-name" MODIFY "columne-name" NOT NULL"

fails with the error mentioned above.

Would be very helpfull if this missing feature could be added.

Regards Sam 

[Sam Blume]

Uff, I must restate my last entry: ALTER TABLE "table-name" MODIFY "columne-name" NULL causes an odd modification.

It changes the colume type to NULL(0)  (never heard of that type)

To reporduce, do this:

   CREATE TABLE  FOO (
     A VARCHAR2(50) NOT NULL,
     B VARCHAR2(50) NOT NULL
  );

  show columns from FOO;

-- You will see:

FIELD	TYPE	NULL	KEY	DEFAULT
A	VARCHAR(50)	NO	null	NULL
B	VARCHAR(50)	NO	null	NULL

-- Now use the Alter table with NULL

  ALTER TABLE FOO MODIFY A NULL;

  show columns from FOO;

-- Columne 'A' is now of TYPE NULL(0)

FIELD	TYPE	NULL	KEY	DEFAULT
A	NULL(0)	NO	null	NULL
B	VARCHAR(50)	NO	null	NULL

[Noel Grandin]

This is our official syntax for such things:

http://h2database.com/html/grammar.html#alter_table_alter_column

​

[Sarkkuli]

I struggled with the same issue for a while, and want to give my notes for this topic. I also tried format ALTER TABLE "table-name" MODIFY "column-name" NULL, which resulted in "Unknown data type: 'NOT'". It seems that h2 is expecting a column type in the MODIFY statement, although the column type is already modified from before, during CREATE TABLE. The following modification fixed the issue:

ALTER TABLE "table-name" MODIFY "column-name" varchar(128) NULL;

[Evgenij Ryazanov]

Hello.

In the SQL Standard the following command is defined:

ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL

H2 supports it properly.

Other commands are vendor-specific and H2 obliviously doesn't support them all.