Modifying the Structure of a Table
☞ Following are the topics to be covered :
1. Adding a Column to an Existing Table
2. Modifying an Existing Table Definition
3. Renaming a Column
4. Removing a Column
5. Adding Primary Key Constraints
6. Deleting Primary Key Constraints
☞ To modify the definition(structure) of a table by modifying the definition of its columns in RDBMS, we use ALTER TABLE command.
Adding a column to an existing table
☞ Syntax :
ALTER TABLE <table_name>
ADD <column_name> <data_type> (size) ;
☞ Example :
ALTER TABLE student
ADD mobile INTEGER(10) ;
Adding a column with default value
☞ Syntax :
ALTER TABLE <table_name>
ADD <column_name> <data_type> (size)
DEFAULT <data> ;
☞ Example :
ALTER TABLE student
ADD city CHAR(6)
DEFAULT "Delhi" ;
Modifying an existing column definition
☞ Syntax :
ALTER TABLE <table_name>
MODIFY <column_name> <data_type> (size) ;
☞ Example :
ALTER TABLE student
MODIFY city VARCHAR(10);
Renaming a column
☞ Syntax :
ALTER TABLE <table_name>
CHANGE <old_column_name> TO <new_column_name> <data_type> (size) ;
☞ Example :
ALTER TABLE student
CHANGE city TO state VARCHAR(10) ;
Removing a column
☞ Syntax :
ALTER TABLE <table_name>
DROP <column_name>;
☞ Example :
ALTER TABLE student
DROP mobile ;
Adding Primary Key constraints :
☞ Syntax :
ALTER TABLE <table_name>
ADD PRIMARY KEY(<column_name>) ;
☞ Example :
ALTER TABLE student
ADD PRIMARY KEY(roll_no) ;
Deleting Primary Key constraints
☞ Syntax :
ALTER TABLE <table_name>
DROP PRIMARY KEY;
☞ Example :
ALTER TABLE student
DROP PRIMARY KEY;
