1
ALTER TABLE
Once a table is created in the database, there are many occasions where one may wish to
change the structure of the table. Typical cases include the following:
- Add a column
- Drop a column
- Change a column name
- Change the data type for a column
Please note that the above is not an exhaustive list. There are other instances where ALTER
TABLE is used to change the table structure, such as changing the primary key specification
or adding a unique constraint to a column.
The SQL syntax for ALTER TABLE is
ALTER TABLE "table_name"
[alter specification]
[alter specification] is dependent on the type of alteration we wish to perform. For the uses
cited above, the [alter specification] statements are:
• Add a column: ADD "column 1" "data type for column 1"
• Drop a column: DROP "column 1"
• Change a column name: CHANGE "old column name" "new column name" "data
type for new column name"
• Change the data type for a column: MODIFY "column 1" "new data type"
Let's run through examples for each one of the above, using the "customer" table created in
the CREATE TABLE section:
Table customer
Column Name
Data Type
First_Name
char(50)
Last_Name
char(50)
Address
char(50)
City
char(50)
Country
char(25)
Birth_Date
date
2
First, we want to add a column called "Gender" to this table. To do this, we key in:
ALTER table customer add Gender char(1)
Resulting table structure:
Table customer
Column Name
Data Type
First_Name
char(50)
Last_Name
char(50)
Address
char(50)
City
char(50)
Country
char(25)
Birth_Date
date
Gender
char(1)
Next, we want to rename "Address" to "Addr". To do this, we key in,
ALTER table customer change Address Addr char(50)
Resulting table structure:
Table customer
Column Name
Data Type
First_Name
char(50)
Last_Name
char(50)
Addr
char(50)
City
char(50)
Country
char(25)
Birth_Date
date
Gender
char(1)
3
Then, we want to change the data type for "Addr" to char(30). To do this, we key in,
ALTER table customer modify Addr char(30)
Resulting table structure:
Table customer
Column Name
Data Type
First_Name
char(50)
Last_Name
char(50)
Addr
char(30)
City
char(50)
Country
char(25)
Birth_Date
date
Gender
char(1)
Finally, we want to drop the column "Gender". To do this, we key in,
ALTER table customer drop Gender
Resulting table structure:
Table customer
Column Name
Data Type
First_Name
char(50)
Last_Name
char(50)
Addr
char(30)
City
char(50)
Country
char(25)
Birth_Date
date
4
ALTER TABLE emp
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY (mgr)
REFERENCES emp (empno) ;
ALTER TABLE emp
DROP CONSTRAINT emp_mgr_fk ;
ALTER TABLE dept
DROP PRIMERY KEY CASCADE ;
ALTER TABLE emp
DISABLE CONSTRAINT emp_empno_pk CASCADE ;
ALTER TABLE emp
ENABLE CONSTRAINT emp_empno_pk ;
ALTER TABLE dept
MODIFY (deptno NUMBER(2) PRIMARY KEY);
