Change null status of an existing column : NOT NULL « Table « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table
3. NOT NULL

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>
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>
SQL> ALTER TABLE product MODIFY data_load_date DATE;

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                                                 DATE

SQL>
SQL>
SQL> UPDATE product SET data_load_date = '15-DEC-2003';

6 rows updated.

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                                                 DATE

SQL>
SQL> ALTER TABLE product MODIFY data_load_date NOT 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                                        NOT NULL DATE

SQL>
SQL>
SQL>
SQL> drop table product;

Table dropped.

SQL>
SQL>