Using Default Values

The Oracle9 i database introduced a new feature that allows you to define a default value for a column. For example, the following statement creates a table named order_status that has two columns named status and last_modified . The status column is defaulted to Order placed and the last_modified column is defaulted to the value returned by the SYSDATE function:

 CREATE TABLE order_status (order_status_id INTEGER  CONSTRAINT default_example_pk PRIMARY KEY,  status VARCHAR2(20) DEFAULT 'Order placed' NOT NULL,  last_modified DATE DEFAULT SYSDATE); 

Note  

As with the other tables featured in this book, the order_status table is created by the store_schema.sql script. This means you don t have to type in the previous CREATE TABLE statement yourself. Also, you don t have to type in the INSERT statements shown later in this section.

If you were to add a new row to the order_status table but didn t specify the values for the status and last_modified columns, those columns would be set to the default values. For example, the following INSERT statement omits values for the status and last_modified columns:

 INSERT INTO order_status (order_status_id) VALUES (1); 

The status column is set to the default value of Order placed and the last_modified column is set to the current date and time returned by the SYSDATE function.

You can override the defaults by specifying a value for the columns, as shown in the following example:

 INSERT INTO order_status (order_status_id, status, last_modified) VALUES (2, 'Order shipped', '10-JUN-2004'); 

The following query retrieves the rows from order_status :

  SELECT *   FROM order_status;  ORDER_STATUS_ID STATUS LAST_MODI --------------- -------------------- ---------  1 Order placed 28-DEC-03  2 Order shipped 10-JUN-04 

You can update a column and set it back to the default using the DEFAULT keyword in an UPDATE statement. For example, the following UPDATE statement sets the status column to the default:

 UPDATE order_status SET status = DEFAULT WHERE order_status_id = 2; 

The following query shows the change made by this UPDATE statement:

  SELECT *   FROM order_status;  ORDER_STATUS_ID STATUS LAST_MODI --------------- -------------------- ---------  1 Order placed 28-DEC-03  2 Order placed 10-JUN-04