Changing the Default Value of a Column

The following ALTER TABLE statement changes the default value for the emp.hiredate column to SYSDATE - 1:


SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    SAL NUMBER(7, 2),
  5                    HIREDATE DATE,
  6                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> ALTER TABLE emp
  2  MODIFY hiredate DEFAULT SYSDATE - 1;

Table altered.

SQL>
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 SAL                                                NUMBER(7,2)
 HIREDATE                                           DATE
 DEPTNO                                             NUMBER(2)

SQL>

The default value applies only to new rows added to the table.

Home »
Oracle »
Table » 

Column:
  1. Adding a Column
  2. Adding a Virtual Column
  3. Changing the Size of a Column
  4. Changing the Precision of a Numeric Column
  5. Changing the Data Type of a Column
  6. Changing the Default Value of a Column
  7. Dropping a Column
Related: