Altering oracle tables

Alter oracle table

We can alter oracle table after it is created. And most of the altering of oracle tables are done on the logical structure of an existing table.

In this topic, we will going to see on how to add, modify, drop, mark unused and rename column of a table. We will also going to see practically on how to rename a table and add constraints to an existing table using  SQL*PLUS  oracle command-line tool.

To begin with, let us first create a table named tblcustomer through SQL*PLUS command-line tool.

Example:
D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 26 19:58:54 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.

SQL> create table tblcustomer (
2 cust_no number generated as identity,
3 customer_type char(1),
4 cust_fullname varchar2(50),
5 country varchar2(30),
6 nationality varchar2(30)
7 );

Table created.

Now, here below are examples of alter oracle table based on the table created above.

 Adding new column named  ‘swift_code’ 

Example:
SQL> Alter table tblcustomer add ( swift_code varchar2(20));

Table altered.

To confirm whether the ‘swift_code‘ column is created or not, run the following SQL command:

Example:
SQL> describe tblcustomer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NO NOT NULL NUMBER
CUSTOMER_TYPE CHAR(1)
CUST_FULLNAME VARCHAR2(50)
COUNTRY VARCHAR2(30)
NATIONALITY VARCHAR2(30)
SWIFT_CODE VARCHAR2(20)

As you can see from the  query above, the new column ‘swift_code‘ is added to tblcustomer table.

 Modifying the data type of column named ‘customer_type’ of tblcustomer table

Example:
SQL> Alter table tblcustomer modify( customer_type varchar2(30));

Table altered.

And then  to  check whether the ‘customer_type‘ column data type is changed or not, run the following SQL command:

Example:
SQL> describe tblcustomer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NO NOT NULL NUMBER
CUSTOMER_TYPE VARCHAR2(30)
CUST_FULLNAME VARCHAR2(50)
COUNTRY VARCHAR2(30)
NATIONALITY VARCHAR2(30)
SWIFT_CODE VARCHAR2(20)

As you can see also from the query above, the data type of column ‘customer_type‘ is changed to VARCHAR2(30).

 Dropping a column named ‘COUNTRY’ from tblcustomer table

Example:
SQL> alter table tblcustomer drop column COUNTRY;

Table altered.

And to confirm whether the ‘COUNTRY‘ column is dropped or not, run the following SQL command:

Example:
SQL> describe tblcustomer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NO NOT NULL NUMBER
CUSTOMER_TYPE VARCHAR2(30)
CUST_FULLNAME VARCHAR2(50)
NATIONALITY VARCHAR2(30)
SWIFT_CODE VARCHAR2(20)

As you can see also  from the  query above, the column ‘COUNTRY‘ is removed from tblcustomer table.

Renaming  column ‘CUST_FULLNAME’ of tblcustomer table

Example:
SQL> alter table tblcustomer rename column CUST_FULLNAME to CUST_NAME;

Table altered.

And then to check whether the ‘CUST_FULLNAME‘ column is renamed or not, run the following SQL command:

Example:
SQL> describe tblcustomer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NO NOT NULL NUMBER
CUSTOMER_TYPE VARCHAR2(30)
CUST_NAME VARCHAR2(50)
NATIONALITY VARCHAR2(30)
SWIFT_CODE VARCHAR2(20)

 Marking column  ‘SWIFT_CODE’  of tblcustomer table unused

Example:
SQL> alter table tblcustomer set unused column SWIFT_CODE;

Table altered.

And to confirm whether the ‘SWIFT_CODE‘ column is marked as unused or not, run the following SQL command:

Example:
SQL> describe tblcustomer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NO NOT NULL NUMBER
CUSTOMER_TYPE VARCHAR2(30)
CUST_NAME VARCHAR2(50)
NATIONALITY VARCHAR2(30)

As you can see also from the query above , the column ‘SWIFT_CODE‘ is not displayed.

 Renaming  table tblcustomer  to tbl_customer

Example:
SQL> alter table tblcustomer rename to tbl_customer;

Table altered.

Marking table  tbl_customer  as read-only

Example:
SQL> alter table tbl_customer read only;

Table altered.

And  then to check whether the ‘tbl_customer‘ table is in read-only mode or not, run the following SQL command:

Example:
SQL> col table_name format a20;
SQL> col read_only format a30;
SQL> select table_name, read_only from user_tables where read_only='YES';

TABLE_NAME READ_ONLY
-------------------- ------------------------------
TBL_CUSTOMER YES

 Marking table  tbl_customer  as read write

Example:
SQL> alter table tbl_customer read write;

Table altered.

 Dropping unused columns from tbl_customer  table(SWIFT_CODE column was marked unused on example05)

Example:
SQL> ALTER TABLE tbl_customer DROP UNUSED COLUMNS;

Table altered.

Adding primary key constraint 

Example:
SQL> alter table tbl_customer add constraint cust_pk primary key (CUST_NO);

Table altered.

How to add CHECK constraint 

Example:
SQL> alter table tbl_customer add constraint chk_cust CHECK (CUSTOMER_TYPE IN ('I','C','B'));

Table altered.

SQL>

Adding UNIQUE constraint 

Example:
SQL> alter table tbl_customer add constraint uk_cust_name UNIQUE (CUST_NAME);

Table altered.