Oracle PL/SQL Tutorial/Table/Add Column

Add a column with NOT NULL constraint

SQL>
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.

Add columns

SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, null);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL> DESC product;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
SQL>
SQL> ALTER TABLE product ADD data_load_date VARCHAR2(8) NULL;
Table altered.
SQL>
SQL> DESC product;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
 DATA_LOAD_DATE                                                 VARCHAR2(8)
SQL>
SQL> drop table product;
Table dropped.
SQL>

Add more columns

SQL>
SQL>
SQL> CREATE TABLE myTable (
  2     Name    VARCHAR(50) PRIMARY KEY NOT NULL,
  3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
  4     SSN     VARCHAR(15) NOT NULL,
  5     CHECK (SSN LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]"));
Table created.
SQL>
SQL>
SQL>
SQL> ALTER TABLE myTable ADD (EMail VARCHAR(25), ICQ VARCHAR(15));
Table altered.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Add new column with default value and not null

SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)
  6  , constraint  R_PK        primary key (attendee,course,begindate)
  7  ) ;
Table created.
SQL>
SQL>
SQL> alter table registrations
  2  add  (entered_by number(4) default 9 not null);
Table altered.
SQL>
SQL>
SQL> drop table registrations;
Table dropped.