With|Not Null and With|Not Default Combinations
The with|not null clause works in combination with the with|not default clause, as follows:
with null
The column accepts nulls. If no value is provided, the DBMS Server inserts a null.
with null with default
The column accepts null values. If no value is provided, the DBMS Server inserts a 0 or blank string, depending on the data type.
with null not default
The column accepts null values. The user must provide a value (mandatory column).
not null with default
The column does not accept nulls. If no value is provided, the DBMS Server inserts 0 for numeric and money columns, or an empty string for character and date columns.
not null not default (or not null)
The column is mandatory and does not accept nulls, which is typical for primary key columns.
System_Maintained Logical Keys
SYSTEM_MAINTAINED logical key columns are assigned values by the DBMS Server, and cannot be assigned values by applications or end users. The following restrictions apply to logical keys specified as with SYSTEM_MAINTAINED:
• The only valid default clause is with default. If the default clause is omitted, with default is assumed.
• The only valid null clause is not null. If a column constraint or null clause is not specified, not null is assumed.
• No table constraint can include a SYSTEM_MAINTAINED logical key column. For details about table constraints, see Column-Level Constraints and Table-Level Constraints in the Ingres SQL Reference Guide.