What is NULL check and insertion rule in a DB2 table?

Null in DB2 is defined as nothing. It is an unknown value. If we want to restrict NULL value in any column then the column should be defined with the “NOT NULL” parameter in CREATE TABLE.

The “NOT NULL” will force the user to enter a value for the column. However, if we do not want to give any value for this column we can also place a “WITH DEFAULT” parameter which will allow DB2 to place the default value in case the user has not provided any value for the “NOT NULL” column.

For example, if we have a column INVOICE_ID which should be not null and also, we want DB2 to insert spaces in this column if the user does not give any value for this column,then we will define the column as below−

CREATE TABLE ORDERS
   (ORDER_ID CHAR(15) NOT NULL,
   ORDER_DATE DATE,
   INVOICE_ID CHAR(15), NOT NULL WITH DEFAULT
   ORDER_TOTAL DECIMAL(9,2),
   TRANSACTION_ID CHAR(15),
   PRIMARY KEY(ORDER_ID),
IN DB4ES01;

The column INVOICE_ID is defined with data type CHAR and the default value taken by DB2 for CHAR is spaces. So in the above case, we have defined INVOICE_ID column as NOT NULL and it will take default value as spaces when an explicit value is not given for this column.

Mandalika

Published on 14-Sep-2020 17:30:08