Oracle PL/SQL Tutorial/Table/Default Column Value

Add a column with default value

SQL>
SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)) ;
Table created.
SQL>
SQL>
SQL> insert into registrations values (1, "SQL",date "1999-04-12",4   );
1 row created.
SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (4, "OAU",date "1999-08-10",4   );
1 row created.
SQL> insert into registrations values (5, "OAU",date "2000-09-27",5   );
1 row created.
SQL> insert into registrations values (6, "JAV",date "1999-12-13",2   );
1 row created.
SQL> insert into registrations values (7, "JAV",date "2000-02-01",4   );
1 row created.
SQL> insert into registrations values (8, "JAV",date "2000-02-01",5   );
1 row created.
SQL> insert into registrations values (9, "XML",date "2000-02-03",4   );
1 row created.
SQL> insert into registrations values (10,"XML",date "2000-02-03",5   );
1 row created.
SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL);
1 row created.
SQL>
SQL>
SQL> alter table registrations
  2  add  (entered_by number(4) default 7839 not null);
Table altered.
SQL>
SQL> drop table registrations;
Table dropped.

Date type column with current date as the default value

SQL>
SQL>
SQL> create table WORK_T (
  2  id                             number,
  3  code                           varchar2(30),
  4  name                           varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );
Table created.
SQL>
SQL>
SQL> drop   table WORK_T;
Table dropped.
SQL>
SQL>
SQL>

Insert record into a table with default value

SQL>
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>
SQL> INSERT INTO myTable (id)
  2  VALUES (1);
1 row created.
SQL>
SQL> select * from myTable;
        ID STATUS               LAST_MODI
---------- -------------------- ---------
         1 Order placed         02-JUN-07
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

null with default value column

SQL>
SQL>
SQL> create table t
  2  (id number primary key,
  3  status char(1) default "Y")
  4  /
Table created.
SQL>
SQL> create sequence s;
Sequence created.
SQL>
SQL> insert into t
  2  (id)
  3  values(s.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> insert into t
  2  (id, status)
  3  values(s.nextval, "N");
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> drop table t;
Table dropped.

Override the defaults by specifying a value for the columns

SQL>
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>
SQL>
SQL> INSERT INTO myTable (id, status, last_modified)
  2  VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL> select * from myTable;
        ID STATUS               LAST_MODI
---------- -------------------- ---------
         2 Order shipped        10-JUN-04
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Update a column and set it back to the default using the DEFAULT keyword in an UPDATE statement

SQL>
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>
SQL> INSERT INTO myTable (id, status, last_modified)
  2  VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL>
SQL> UPDATE myTable
  2  SET status = DEFAULT;
1 row updated.
SQL>
SQL> select * from myTable
  2
SQL> drop table myTable;
Table dropped.
SQL>

Update statement with default

SQL>
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>
SQL>
SQL> INSERT INTO myTable (id, status, last_modified)
  2  VALUES (2, "Order shipped", "10-JUN-2004");
1 row created.
SQL>
SQL>
SQL>
SQL> UPDATE myTable
  2  SET status = DEFAULT;
1 row updated.
SQL>
SQL> select * from myTable
  2
SQL> drop table myTable;
Table dropped.
SQL>

Using Default Values

Define a default value for a column.

last_modified column is defaulted to the value returned by the SYSDATE function.

SQL>
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> drop table myTable;
Table dropped.