You can use the CREATE TABLE statement to create column defaults at the time a table is created, or the ALTER TABLE statement to add column defaults at a later time. You can also specify a default value when creating a user-defined domain (data type) using the CREATE DOMAIN statement.
The stored procedure sp_iqcolumn returns information abut all columns for all tables. One of the column returned by the result set of sp_iqcolumn is called “default”, and shows the particular default value for that column.
The following statement creates a table named tab1 with the default special value LAST USER specified for the CHARACTER column c1:
CREATE TABLE tab1(c1 CHAR(20) DEFAULT LAST USER)
The following statement adds a condition to an existing column named id in the sales_order table, so that the value of the column automatically increments (unless a client application specifies a value):
ALTER TABLE sales_order MODIFY id DEFAULT AUTOINCREMENT
The following statement defines a domain named dom1with a data type of INTEGER and a default value of 45:
CREATE DOMAIN dom1 INTEGER DEFAULT 45
Each of the other default values is specified in a similar manner. For more information, see ALTER TABLE statement, CREATE TABLE statement, and CREATE DOMAIN statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options.
