SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-77
Defining Columns That Allow or Prohibit Null
Defining Columns That Allow or Prohibit Null
The CREATE TABLE and ALTER TABLE statements define the attributes for columns
within tables. A column allows nulls unless the column definition includes the NOT
NULL clause or the column is part of the primary key of the table.
Null is the default for a column (other than NOT NULL) unless the column definition
includes either a DEFAULT clause (other than DEFAULT NULL) or the NO DEFAULT
clause. The default value for a column is the value NonStop SQL/MX inserts in a row
when an INSERT statement omits a value for a particular column.
Determining Whether a Column Allows Null
To determine whether a column accepts null, use the INVOKE command to list the
table description and check the column definitions. See INVOKE Command on
page 4-33.
This INVOKE example illustrates how to display information about whether columns
allow or prohibit null. The display shows NOT NULL for columns whose definition
prohibits null.
INVOKE PERSNL.EMPLOYEE;
-- Definition of table SAMDBCAT.PERSNL.EMPLOYEE
-- Definition current Mon Sep 22 13:44:08 1997
(
EMPNUM NUMERIC(4, 0) UNSIGNED NO DEFAULT
HEADING 'Employee/Number' NOT NULL NOT DROPPABLE
,FIRST_NAME CHAR(15) DEFAULT _ISO88591' '
HEADING 'First Name' NOT NULL NOT DROPPABLE
,LAST_NAME CHAR(20) DEFAULT _ISO88591' '
HEADING 'Last Name' NOT NULL NOT DROPPABLE
,DEPTNUM NUMERIC(4, 0) UNSIGNED NO DEFAULT
HEADING 'Dept/Num' NOT NULL NOT DROPPABLE
,JOBCODE NUMERIC(4, 0) UNSIGNED DEFAULT NULL
HEADING 'Job/Code'
,SALARY NUMERIC(8, 2) UNSIGNED DEFAULT NULL
)
--- SQL operation complete.
In the preceding example, the columns EMPNUM, FIRST_NAME, LAST_NAME, and
DEPTNUM are defined as NOT NULL. The columns JOBCODE and SALARY are
allowed to be null.