0

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

1
  • Which RDBMS are you using?
    – Philᵀᴹ
    Mar 28, 2013 at 9:38

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.