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 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 ... );
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 type||Default value|
|SMALLINT, INTEGER, DECIMAL, NUMERIC, NUM, REAL, FLOAT, DOUBLE, DOUBLE RECISION||0|
|CHAR, GRAPHIC||A string of blank characters|
|VARCHAR, LONG VARCHAR, VARGRAPHIC, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB||A zero-length string|
|DATE||The current time( |
|TIME||The current time( |
|TIMESTAMP||The current timestamp ( |
|user-defined data type||The 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
If the column does not have the
NOT NULL constraint. Its default value is NULL.
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) );
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
INSERT INTO reviews(book_id, user_id, review) VALUES (1,1,'This book is excellent that contains many useful information');
Third, query data from the
SELECT review_id, book_id, user_id, review, review_at FROM reviews;
Here is the output:
As you can see clearly from the output, the
review_at column received the current timestamp.
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;
Consider the following example:
First, add the
rating column to the
ALTER TABLE reviews ADD COLUMN rating SMALLINT;
Then, add a default constraint to the
ALTER TABLE reviews ALTER COLUMN rating SET DEFAULT 3;
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;
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.