Feature request: alter column "drop not null"

[GoogleCodeExporter]

I created this table with client_id NOT NULL:

CREATE TABLE client_photos
(
  id bigint NOT NULL,
  photo bytea NOT NULL,
  client_id bigidnt NOT NULL,
  CONSTRAINT client_photos_pkey PRIMARY KEY (id)
);

Now how can I drop the NOT NULL constraint without losing data?

PostgreSQL has this syntax:

ALTER TABLE client_photos ALTER COLUMN client_id DROP NOT NULL;

H2 complains about the "DROP NOT NULL" expression. Could H2 support this?

The work-around I have found is to create a new column without the
constraint, update its content from the original column, drop the original
column, then rename the new column to the original name.

Original issue reported on code.google.com by nicolas....@gmail.com on 27 Apr 2010 at 2:22

[GoogleCodeExporter]

Hi,

This is supported, but the syntax is a bit different than PostgreSQL.

See http://www.h2database.com/html/grammar.html#alter_table_alter

SET NULL sets a column to allow NULL. The row may not be part of a primary key.
Single column indexes on this column are dropped.

SET NOT NULL sets a column to not allow NULL. Rows may not contains NULL in 
this column.

In the next release, the PostgreSQL syntax will be supported as well.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 29 Apr 2010 at 6:22

• Changed state: Accepted

[GoogleCodeExporter]

Implemented in version 1.2.135.

Original comment by thomas.t...@gmail.com on 8 May 2010 at 1:16

• Changed state: Fixed