{"id":680,"date":"2019-06-05T19:51:51","date_gmt":"2019-06-06T03:51:51","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=680"},"modified":"2020-04-11T06:58:33","modified_gmt":"2020-04-11T14:58:33","slug":"db2-default-constraint","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-default-constraint\/","title":{"rendered":"Db2 DEFAULT Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 DEFAULT<\/code> constraint to specify a value inserted into a column if the application does not provide the value.<\/p>\n\n\n\n

Introduction to Db2 DEFAULT constraint<\/h2>\n\n\n\n

The DEFAULT<\/code> constraint specifies a predefined value inserted into a column if the application doesn’t supply a value.<\/p>\n\n\n\n

The following illustrates how to add a DEFAULT<\/code> constraint to a column when you create a new table<\/a>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name (\n    ...,\n    column_name type<\/span> DEFAULT<\/span> default_value\n    ...\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you need to specify the DEFAULT<\/code> keyword following by the default value. The default value depends on the data type of the column.<\/p>\n\n\n\n
This table illustrates the data types and their corresponding default values:<\/p>\n\n\n\n
Column data type<\/strong><\/td>Default value <\/strong><\/td><\/tr><\/thead>
SMALLINT<\/kbd>, INTEGER<\/kbd><\/a>, DECIMAL<\/kbd>, NUMERIC<\/kbd>, NUM<\/kbd>, REAL<\/kbd>, FLOAT<\/kbd>, DOUBLE<\/kbd>, DOUBLE RECISION<\/kbd><\/td>0<\/td><\/tr>
CHAR<\/kbd><\/a>, GRAPHIC<\/kbd><\/td>A string of blank characters<\/td><\/tr>
VARCHAR<\/kbd><\/a>, LONG VARCHAR<\/kbd>, VARGRAPHIC<\/kbd>, LONG VARGRAPHIC<\/kbd>, BLOB<\/kbd>, CLOB<\/kbd>, DBCLOB<\/kbd><\/td>A zero-length string<\/td><\/tr>
DATE<\/kbd><\/a><\/td>The current time( CURRENT_DATE<\/code>) when the row is inserted into the table. Existing rows receive the date 0001-01-01<\/td><\/tr>
TIME<\/kbd><\/a><\/td>The current time( CURRENT_TIME<\/code>) when the row is inserted into the table. Existing rows receive the time 00:00:00<\/td><\/tr>
TIMESTAMP<\/kbd><\/a><\/td>The current timestamp ( CURRENT_TIMESTAMP<\/code>) when the row is inserted into the table.<\/td><\/tr>
XML<\/kbd><\/td>Not applicable<\/td><\/tr>
user-defined data type<\/td>The default value of the built-in data type on which the distinct user-defined data type is based<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Db2 applies the default constraints when new rows are inserted into the table via INSERT<\/code>, IMPORT<\/code>, LOAD<\/code> or INGEST<\/code> commands.<\/p>\n\n\n\n
If the column does not have the NOT NULL<\/code> constraint. Its default value is NULL.<\/p>\n\n\n\n
Db2 DEFAULT constraint example<\/h2>\n\n\n\n
First, create a new table<\/a> named reviews<\/code> to store book reviews:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> reviews(\n    review_id INT<\/span> NOT<\/span> NULL<\/span> \n        GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span>,\n    book_id INT<\/span> NOT<\/span> NULL<\/span>,\n    user_id INT<\/span> NOT<\/span> NULL<\/span>,\n    review VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    review_at TIMESTAMP<\/span> NOT<\/span> NULL<\/span> \n        DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>,   \n    FOREIGN<\/span> KEY<\/span> (book_id) \n        REFERENCES<\/span> books(book_id),\n    PRIMARY KEY<\/span>(review_id)   \n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the reviews<\/code> table, the review_at<\/code> has a default value as the CURRENT_TIMESTAMP<\/code>. It means that if you don’t supply a value to the review_at<\/code> column, the Db2 will use the current time to insert into this column.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> \n    reviews(book_id, user_id, review)\nVALUES<\/span>\n    (1<\/span>,1<\/span>,'This book is excellent that contains many useful information'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the reviews<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n    review_id,\n    book_id,\n    user_id,\n    review,\n    review_at\nFROM<\/span>\n    reviews;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
As you can see clearly from the output, the review_at<\/code> column received the current timestamp.<\/p>\n\n\n\n
Adding the DEFAULT constraint to a column<\/h2>\n\n\n\n
To add the default constraint to an existing column, you use the following ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name \nALTER<\/span> COLUMN<\/span> column_name \nSET<\/span> DEFAULT<\/span> default_value;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Consider the following example:<\/p>\n\n\n\n
First, add the rating<\/code> column to the reviews<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> reviews\nADD<\/span> COLUMN<\/span> rating SMALLINT<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, add a default constraint to the rating<\/code> column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> reviews\nALTER<\/span> COLUMN<\/span> rating SET<\/span> DEFAULT<\/span> 3<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to add a new column with a default value, you can do it in one step:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> reviews\nADD<\/span> COLUMN<\/span> RATING SMALLINT<\/span> DEFAULT<\/span> 3<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Db2 DEFAULT<\/code> constraint to specify a value to be inserted into a column when the application doesn’t supply the value.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Second, insert<\/a> a new review to the reviews<\/code> table:<\/p>\n\n\n