how to check for not null column constraint in oracle sql?

Question

How do i check if a column in a table has a not null constraint in an oracle db? Can it be checked with the data dictionary?

This question was asked before, take a look: [stackoverflow nullable column][1] [1]: http://stackoverflow.com/questions/1028956/modify-column-in-oracle-how-to-check-if-a-column-is-nullable-before-setting-to — matlabit, Mar 02 '12 at 01:19

score 19 · Answer 1 · answered Mar 02 '12 at 01:17

19

SELECT nullable
  FROM all_tab_cols
 WHERE owner = <<owner of table>>
   AND table_name = <<name of table>>
   AND column_name = <<name of column>>

will work assuming the column is marked NOT NULL rather than, say, having a CHECK constraint that checks that it is non-NULL.

answered Mar 02 '12 at 01:17

Justin Cave

3

People who use check constraints rather than column DDL to define not null columo.ns should be severely spoken to. – APC Mar 02 '12 at 10:11
1

@APC, agreed, but internally Oracle represents those `NOT NULL` columns with check constraints. They're utterly indistinguishable from `ALL_CONSTRAINTS`. RRgghgh. >:-( – Dan Lenski Sep 09 '16 at 22:35

1 Answers1