NULL Values

NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. A NULL should not be confused with a value of 0. A NULL value indicates a lack of a value, which is not the same thing as a value of zero. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "NULL" (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by NULL), and it would not be updated with the value "zero" until we have ascertained that Adam owns no books.
(Wikipedia)

In other words, SQL NULL is a state, not a value. This usage is quite different from most programming languages, where null value of a reference means it is not pointing to any object.

By default, the RDM database schema enables the usage of NULL. For every nullable column, a companion column is generated that contains a boolean to indicate whether the column position contains data or not.

If the column is defined as NOT NULL, the companion column is eliminated and the column position is assumed to always contain data. For example, the following schema allows for the name and age_yrs columns to be nullable:

create table t1
(
   id rowid primary key,
   name char(20),
   age_yrs int
);

The resulting C header file generated by the rdm-compile tool using --c-structs option contains the following snippet defining the row structure:

/** \brief struct definition for table T1 */
typedef struct T1_S
{
    RDM_ROWID_T ID;
    char NAME[81];
    int32_t AGE_YRS;
    RDM_HAS_VALUE_T _NAME_has_value;
    RDM_HAS_VALUE_T _AGE_YRS_has_value;
} T1;

By adding the NOT NULL constraint as shown below:

create table t1
(
   id rowid primary key,
   name char(20) not null,
   age_yrs int not null
);

The resulting C header file will not have the additional structure members defined.

/** \brief struct definition for table T1 */
typedef struct T1_S
{
    RDM_ROWID_T ID;
    char NAME[81];
    int32_t AGE_YRS;
} T1;

If a column is defined as NULL capable, no data will every be retained in the associated column when the row is written to the database. For non-SQL CLI programming, the developer MUST check the '*_has_value' member to determine if the result read from the database is valid data or not.

Columns designated as PRIMARY KEY cannot have a NULL state and are defined automatically as NOT NULL.

Recommendations

• Applications that do not need to know whether a field has been populated or not, setting ALL columns to NOT NULL would be recommended.
• For C or C++ applications, setting ALL columns to NOT NULL simplifies the insert, update and read processes. Note that a read process MUST check the "*_has_value" flag to verify that it is TRUE before using the associated column value.