H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

alter-tableconstrainth2null

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the NOT NULL columns and I don't seem to find anything similar to check the constraint on the table columns.

Notice I don't want to check the NOT NULL column values with something like SELECT COLUMN FROM TABLE WHERE COLUMN IS NOT NULL! I want to check the table definition for that specific column.

thank you

Best Answer

If you have a definition like this:

create table foo 
(
   id integer not null
)

The following query will show "NO" in the is_nullable column:

select table_name, 
       column_name,
       is_nullable
from information_schema.columns

If the column has a check constraint, like this:

create table foo
(
  id integer,
  constraint id_not_null check (id is not null)
);

then this will be shown as "nullable" with the first query. You would need to check information_schema.constraints and analyze the content of the CHECK_EXPRESSION column to detect this.

Related Question