Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Which RDBMS are you using? –  Phil Mar 28 '13 at 9:38

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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