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:

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:

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.