Db2 DEFAULT Constraint

Summary: in this tutorial, you will learn how to use the Db2 DEFAULT constraint to specify a value inserted into a column if the application does not provide the value.

Introduction to Db2 DEFAULT constraint

The DEFAULT constraint specifies a predefined value inserted into a column if the application doesn’t supply a value.

The following illustrates how to add a DEFAULT constraint to a column when you create a new table:

CREATE TABLE table_name ( ..., column_name type DEFAULT default_value ... );
Code language: SQL (Structured Query Language) (sql)

In this syntax, you need to specify the DEFAULT keyword following by the default value. The default value depends on the data type of the column.

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

Column data typeDefault value 
SMALLINT, INTEGER, DECIMAL, NUMERIC, NUM, REAL, FLOAT, DOUBLE, DOUBLE RECISION0
CHAR, GRAPHICA string of blank characters
VARCHAR, LONG VARCHAR, VARGRAPHIC, LONG VARGRAPHIC, BLOB, CLOB, DBCLOBA zero-length string
DATEThe current time( CURRENT_DATE) when the row is inserted into the table. Existing rows receive the date 0001-01-01
TIMEThe current time( CURRENT_TIME) when the row is inserted into the table. Existing rows receive the time 00:00:00
TIMESTAMPThe current timestamp ( CURRENT_TIMESTAMP) when the row is inserted into the table.
XMLNot applicable
user-defined data typeThe default value of the built-in data type on which the distinct user-defined data type is based

Db2 applies the default constraints when new rows are inserted into the table via INSERT, IMPORT, LOAD or INGEST commands.

If the column does not have the NOT NULL constraint. Its default value is NULL.

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) );
Code language: SQL (Structured Query Language) (sql)

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');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the reviews table:

SELECT review_id, book_id, user_id, review, review_at FROM reviews;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 Default Constraint example

As you can see clearly from the output, the review_at column received the current timestamp.

Adding the DEFAULT constraint to a column

To add the default constraint to an existing column, you use the following ALTER TABLE statement:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
Code language: SQL (Structured Query Language) (sql)

Consider the following example:

First, add the rating column to the reviews table:

ALTER TABLE reviews ADD COLUMN rating SMALLINT;
Code language: SQL (Structured Query Language) (sql)

Then, add a default constraint to the rating column:

ALTER TABLE reviews ALTER COLUMN rating SET DEFAULT 3;
Code language: SQL (Structured Query Language) (sql)

If you want to add a new column with a default value, you can do it in one step:

ALTER TABLE reviews ADD COLUMN RATING SMALLINT DEFAULT 3;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 DEFAULT constraint to specify a value to be inserted into a column when the application doesn’t supply the value.

Was this tutorial helpful ?