DB2 - DDL with Default Statement

---

---

---

DB2 DDL with Default Statement

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)

This table illustrates the data types and their corresponding default values -

For columns of...	Data types	Default
Numbers	SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE, DECFLOAT, or FLOAT	0
Fixed-length strings	CHAR or GRAPHIC BINARY	Blanks Hexadecimal zeros
Varying-length strings	VARCHAR, CLOB, VARGRAPHIC, DBCLOB, VARBINARY, or BLOB	Empty string
Dates	DATE	CURRENT DATE
Times	TIME	CURRENT TIME
Timestamps	TIMESTAMP	CURRENT TIMESTAMP
ROWIDs	ROWID	Db2-generated

What is DB2 DDL with Default Statement ?

The DEFAULT clause allows you to specify the default value for a column. The default value is assigned if you omit the column or specify DEFAULT keyword in a INSERT statement.

The DEFAULT clause in CREATE TABLE and ALTER TABLE statements in Oracle and SQL Server may use a different syntax and require conversion.

DB2 DDL with Default Statement. 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.

Db2 DEFAULT constraint example

First, create a new table named reviews to store book reviews -

CREATE TABLE reviews(
    review_id INT NOT NULL 
        GENERATED ALWAYS AS IDENTITY,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    review VARCHAR(255) NOT NULL,
    review_at TIMESTAMP NOT NULL 
        DEFAULT CURRENT_TIMESTAMP,   
    FOREIGN KEY (book_id) 
        REFERENCES books(book_id),
    PRIMARY KEY(review_id)   
);

In the reviews table, the review_at has a default value as the CURRENT_TIMESTAMP. It means that if you don’t supply a value to the review_at column, the Db2 will use the current time to insert into this column.

Second, insert a new review to the reviews table -

INSERT INTO 
    reviews(book_id, user_id, review)
VALUES
    (1,1,'This book is excellent that contains many useful information');

---

---