Changing the Default Value of a Column : Alter Table « Table « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Table
  3. Alter Table

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

SQL> CREATE TABLE myTable (
  2    id INTEGER,
  3    status VARCHAR2(20) DEFAULT 'Order placed' NOT NULL,
  4    last_modified DATE DEFAULT SYSDATE
  5  );

Table created.

SQL>
SQL> desc myTable;
 Name               Null?    Type
 -------------------

 ID                          NUMBER(38)
 STATUS             NOT NULL VARCHAR2(20)
 LAST_MODIFIED               DATE

SQL>
SQL>
SQL> ALTER TABLE myTable
  2  MODIFY last_modified DEFAULT SYSDATE - 1;

Table altered.

SQL>
SQL> desc myTable;
 Name                Null?    Type
 --------------------

 ID                           NUMBER(38)
 STATUS              NOT NULL VARCHAR2(20)
 LAST_MODIFIED                DATE

SQL>
SQL> drop table myTable;

Table dropped.

SQL>








6.3.Alter Table
6.3.1.Altering a Table
6.3.2.Some of the aspects of a column you can modify using ALTER TABLE
6.3.3.Adding a Column
6.3.4.ADD initially_created DATE DEFAULT SYSDATE NOT NULL;
6.3.5.Changing the Size of a Column
6.3.6.Decrease the length of a column
6.3.7.Changing the Precision of a Numeric Column
6.3.8.Decrease the precision of a numeric column
6.3.9.Changing the Data Type of a Column
6.3.10.Fill data into new added table column
6.3.11.Changing the Default Value of a Column
6.3.12.Alter table to add new constraint for new added column
6.3.13.ALTER TABLE to DISABLE a CONSTRAINT
6.3.14.Alter table to add primary key
6.3.15.Alter table to add primary key across more than one columns
6.3.16.Dropping a Column
6.3.17.New added columns are empty
6.3.18.Alter table to add unique with tablespace and storage setting
6.3.19.Alter table to add primary key with tablespace and storage setting
6.3.20.alter table nologging