DDL with Default


WITH DEFAULT is used to insert a default value when no value given during the INSERT. If column declaration not having WITH DEFAULT and no value to insert, DB2 will not provide any default values during the INSERT. If column declaration having WITH DEFAULT, DB2 will provide default values based on the data type during the INSERT.

Syntax -


CREATE TABLE Table name
	(Column1 data-type [NOT][NULL] [WITH DEFUALT default-value],
	 Column2 data-type [NOT][NULL] [WITH DEFUALT default-value],
	 Column3 data-type [NOT][NULL] [WITH DEFUALT default-value],
		.
		.
	 Column-n data-type [NOT][NULL] [WITH DEFUALT default-value]
	 Primary key/foreign key column1,column2….column-n,
	 [LIKE reference table name]
 ON database-name.tablespace-name)

The default values for data type as like blow.

DDL With DEFAULT -

DDL with Default Statement

In the above, the default values can be specified based on the data types which are called as a system defined default values. There is another way of defaulting a column is user specified values.

If user specified any user specified value WITH DEFAULT parameter, the system will ignore the system default value and value specified will act as a default value for the column.