|
ALTER
ALTER TABLE
ALTER TABLE statement is used to modify the structure of a Table.
1. MODIFY Columns
MODIFY statement used to modify the Column in a Table.
Syntax ::
ALTER TABLE <TABLE>
MODIFY (<COLUMN1> [<DATA TYPE>] [<COLUMN1 CONSTRAINT>],
<COLUMN2> [<DATA TYPE>] [<COLUMN2 CONSTRAINT>],
.....,
<COLUMNX> [<DATA TYPE>] [<COLUMNX CONSTRAINT>]);
Example1 ::
ALTER TABLE CUSTOMER
MODIFY ( CUST_ID NUMBER(4) UNIQUE,
CUST_NAME VARCHAR2(20) ,
CUST_CODE VARCHAR2(6) NOT NULL);
|
Example 2::
ALTER TABLE CUSTOMER
MODIFY ( CUST_ID NUMBER(4) PRIMARY KEY);
|
2. ADD Columns
ADD statement used to add new attributes or columns in a Table.
Syntax::
ALTER TABLE <TABLE>
ADD (<COLUMN1> [<DATA TYPE>] [<COLUMN1 CONSTRAINT>],
<COLUMN2> [<DATA TYPE>] [<COLUMN2 CONSTRAINT>],
.....,
<COLUMNX> [<DATA TYPE>] [<COLUMNX CONSTRAINT>]);
Example 1::
ALTER TABLE CUSTOMER
ADD ( CUST_JOB NUMBER(4) PRIMARY KEY);
|
Example 2::
ALTER TABLE CUSTOMER
ADD ( CUST_JOB VARCHAR2(20) ,
CUST_SAL NUMBER(5,2) NOT NULL);
|
3. DROP COLUMN
DROP COLUMN statement used to drop the columns in the existing Table.
Syntax::
ALTER TABLE <TABLE>
DROP COLUMN <COLUMN>
Example ::
ALTER TABLE CUSTOMER
DROP COLUMN CUST_SAL;
|
4.RENAME Columns
RENAME COLUMN statement used to rename the columns in the existing Table.
Syntax::
ALTER TABLE <TABLE>
RENAME COLUMN <COLUMN>
Example ::
ALTER TABLE CUSTOMER
RENAME COLUMN CUST_SAL CUST_SALARY;
|
Note
Statements like RENAME COLUMN,DROP COLUMN available from ORACLE 9i version onwards.
|
|
