ALTER TABLE Statement

The SQL ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table. You can add or remove Pirmary Key, Foreign Key by using ALTER TABLE Comman.

1. Alter table to add Columns in Tables

SQL> alter table people
  2  add (
  3    phone_number    varchar2(10)
  4  );

Table altered.

2. Add two columns to a table

SQL> alter table people
  2  add(
  3    t_name varchar2(40),
  4    st_name varchar2(15)
  5  );

Table altered.

3. Use alter table command to add foreign key constraint

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id, supplier_name)
  4    REFERENCES supplier(supplier_id, supplier_name);

Table altered.

4. Use alter table to add foreign key with cascade delete

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id)
  4    REFERENCES supplier(supplier_id)
  5    ON DELETE CASCADE;

Table altered.

5. Alter table to add a foreign key ON DELETE SET NULL

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id)
  4    REFERENCES supplier(supplier_id)
  5    ON DELETE SET NULL;

Table altered.

6. Alter table to drop unused columns

select * from user_unused_col_tabs;

ALTER TABLE people DROP UNUSED COLUMNS;

7. Alter table to drop two columns in a single command

alter table people drop (first_name, last_name);

8. Marking Columns Unused

alter table people
set unused (first_name, last_name);

9. Alter table to change the storage

alter table t storage ( FREELISTS 2 );