What is a WITH Default?

The WITH DEFAULT option allows us to specify a default value for a column when creating or altering a table. This means that if no value is provided for this column when a row is inserted, DB2 will automatically assign the specified default value. This is useful for ensuring that certain columns have meaningful default values, improving data consistency, and simplifying data entry.

The WITH DEFAULT option assigns a predefined value to a column when no other value is provided during an insert operation. The default value can be:

• A constant (e.g., a specific number or text).
• System-generated values (like the current date or time).
• NULL if no value is preferred.

Purpose:

• Data Consistency: Ensures that columns always have a value, even if not provided explicitly.
• Ease of Use: Simplifies data entry by automatically assigning a value to specified columns.
• Control Over Null Values: Avoids null entries by providing an alternative default value.

Creating a Table with the WITH DEFAULT

When creating a table, you can specify the WITH DEFAULT option for one or more columns. Each column can have a unique default value, or it can default to NULL.

Syntax -

CREATE TABLE table_name (
column1 data_type [NOT NULL] DEFAULT default_value,
column2 data_type [NOT NULL] DEFAULT default_value,
...
);

• table_name: The name of the table.
• column: Column for which the default value is defined.
• data_type: Data type of the column (e.g., INTEGER, CHAR, DATE).
• DEFAULT default_value: Specifies the default value for the column. This can be a constant, system-generated value (like CURRENT DATE), or NULL.

Examples - Creating a Table with Default Values

CREATE TABLE EMPLOYEE (
EMP_ID INTEGER NOT NULL,
EMP_NAME CHAR(50) DEFAULT 'UNKNOWN',
DEPARTMENT CHAR(30) DEFAULT 'UNASSIGNED',
SALARY DECIMAL(10, 2) DEFAULT 30000.00,
JOIN_DATE DATE DEFAULT CURRENT DATE
);

• EMP_NAME: Defaults to 'UNKNOWN' if no name is provided.
• DEPARTMENT: Defaults to 'UNASSIGNED' if no department is specified.
• SALARY: Defaults to 30000.00 if no salary is provided.
• JOIN_DATE: Defaults to the current date if no joining date is specified.

Altering a Column to Add or Change a WITH DEFAULT

To modify a column to add or update a default value, use the ALTER TABLE statement. This allows us to set or change the default value for an existing column.

Syntax - Altering a Column to Add/Change a WITH DEFAULT Option

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Examples - Altering a column to Add/Change a default value.

ALTER TABLE EMPLOYEE
ALTER COLUMN SALARY SET DEFAULT 35000.00;

Changes the default salary value to 35000.00 for any new rows inserted without a specified salary.

Removing a Default Value

If we no longer want a column to have a default value, we can use DROP DEFAULT.

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Examples - Removing a Default Value

ALTER TABLE EMPLOYEE
ALTER COLUMN DEPARTMENT DROP DEFAULT;

Removes the default value for the DEPARTMENT column, meaning it will no longer default to 'UNASSIGNED'.

Deleting a Table with WITH DEFAULT Option

To delete a table that has columns with default values, simply use the DROP TABLE statement. Dropping the table will delete all columns, data, and default definitions.

Syntax - Deleting a Table

DROP TABLE table_name;

Examples - Deleting a Table

DROP TABLE EMPLOYEE;

---