NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
N-8
Determining Whether a Column Allows Nulls
The NonStop SQL/MP Installation and Management Guide discusses defining columns
with the NULL and DEFAULT clauses in detail.
Determining Whether a Column Allows Nulls
To determine whether a column accepts null values, you can query the COLUMNS
catalog table or you can use INVOKE to list the table description in SQL format (the
default format from SQLCI) and check the column definitions. The COLUMNS table
contains descriptions of all columns of all tables registered in a catalog (as recorded in
the TABLES catalog table). The one-character NULLALLOWED column contains a Y
if a null value is allowed, and an N if not.
The following examples illustrate how to display information through SQLCI about
whether columns allow or prohibit null values:
•
The following example queries the value of the NULLALLOWED column in the
COLUMNS catalog table for the description of a particular column in a particular
table. The example uses the LIKE predicate to avoid entering the whole, exact table
name for the OD2 table. The column of interest is the DELIV_DATE column in
table OD2.
>> SELECT NULLALLOWED FROM COLUMNS
+> WHERE TABLENAME LIKE "%OD2%" AND
+> COLNAME = "DELIV_DATE";
NULLALLOWED
-----------
Y
--- 1 row(s) selected.
•
The following example queries the COLUMNS catalog table to display the value for
the NULLALLOWED column for all the columns of a particular table:
>> SELECT TABLENAME, COLNAME, NULLALLOWED
+> FROM COLUMNS
+> WHERE TABLENAME LIKE "%OD2%";
CC INTEGER NO DEFAULT Allows nulls
CD INTEGER DEFAULT SYSTEM NOT
NULL
Prohibits nulls
CF INTEGER DEFAULT NULL Allows nulls, default
null