Once the table has been created, what if you want to do changes to the column name, add columns, or rename table name itself, is there any way to do that? Yes, we make changes to table names, definitions using SQL ALTER TABLE statement. This statement is used to add, modify and delete columns in a table. There are different syntaxes in different implementations.
1. Add column to table
We can add one or more columns to a table using ALTER command.
Syntax:
ALTER TABLE table_name ADD column_name1 col-definition, Column_name2 col-definition … Column_nameN col-definition; |
Example:
Consider Employee table with below structure. Suppose you want to add one more column Level with data type as varchar(5) and can be NULL.
Field | Type | Null | Key | Default |
EMP_ID | int(11) | NO | PRI | NULL |
EMP_NAME | varchar(20) | NO |
| NULL |
AGE | int(11) | NO |
| NULL |
PHONE_NUM | int(11) | YES |
| NULL |
DEPT_ID | int(11) | NO | MUL | NULL |
SALARY | int(11) | YES |
| NULL |
mysql> ALTER TABLE EMPLOYEE -> ADD LEVEL VARCHAR(5); Can check the description of EMPLOYEE table.
|
Now add multiple columns like marital_sts, and area
mysql> ALTER TABLE EMPLOYEE -> ADD COLUMN (MARITAL_STS VARCHAR(10) NOT NULL, -> AREA VARCHAR(5));
|
2. Delete column from a table
We can delete the column from a table using below syntax.4
In ORACLE and SQL Server: ALTER TABLE table_name DROP COLUMN column_name; In MySQL: ALTER TABLE table_name DROP column_name; |
Suppose we want to drop the column AREA from employee table.
mysql> ALTER TABLE EMPLOYEE -> DROP AREA;
|
3. Modify column in a table
Using this statement, we can change the data type of a column in table. We can change multiple column’s data types.
In ORACLE and MySQL ALTER TABLE table_name MODIFY (col1 column_type, Col2 column_type, Col3 column_type, ….. ColN column_type);
In SQL Server: ALTER TABLE table_name ALTER COLUMN (col1 column_type, Col2 column_type, Col3 column_type, ….. ColN column_type); |
Example:
1. Modify the data type of AGE from int(11) to int(4).
mysql> ALTER TABLE EMPLOYEE -> MODIFY AGE INT(4);
|
2. Modify multiple columns data type: EMP_NAME to varchar(25) and DEPT_ID to int(2). Cant modify multiple columns in MySQL. This can be achieved in ORACLE.
ALTER TABLE EMPLOYEE MODIFY (EMP_NAME varchar2(25), DEPT_ID int(2)); |
4. Rename column in a table
Suppose we want to change the name of the column. We can rename columns in a table using below syntax. We can rename only 1 column at a time.
Syntax
In ORACLE: ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
In MySQL: ALTER TABLE table_name CHANGE col1 col2 [data_type]; Not allowed to rename column in SQL server. |
Example
Consider EMPLOYEE table with below structure, here you can see there are mistakes in column name. Correct the field names, EMMP_NAME to EMP_NAME, AGEG to AGE and SAL to SALARY.
Field | Type | Null | Key | Default |
EMP_ID | int(11) | NO | PRI | NULL |
EMMP_NAME | varchar(20) | NO |
| NULL |
AGEG | int(11) | NO |
| NULL |
PHONE_NUM | int(11) | YES |
| NULL |
DEPT_ID | int(11) | NO | MUL | NULL |
SAL | int(11) | YES |
| NULL |
mysql> ALTER TABLE EMPLOYEE -> CHANGE COLUMN SAL SALARY INT(11);
mysql> ALTER TABLE EMPLOYEE -> CHANGE COLUMN EMMP_NAME EMP_NAME VARCHAR(20);
mysql> ALTER TABLE EMPLOYEE -> CHANGE COLUMN AGEG AGE INT(11); |
We can check the table structure using DESC statement.
Field | Type | Null | Key | Default |
EMP_ID | int(11) | NO | PRI | NULL |
EMP_NAME | varchar(20) | NO |
| NULL |
AGE | int(11) | NO |
| NULL |
PHONE_NUM | int(11) | YES |
| NULL |
DEPT_ID | int(11) | NO | MUL | NULL |
SALARY | int(11) | YES |
| NULL |
5. Rename a table
Using this command we can change the name of a table.
Syntax:
ALTER TABLE old_table_name RENAME TO new_table_name; |
Example:
Suppose we want to change the name of DEPARTMENTS table to DEPTS.
First let us check the table names in database.
+-------------------------------------+ | Tables_in_employeedb | +-------------------------------------+ | co_detail | | departments | | employee | +-------------------------------------+
mysql> ALTER TABLE DEPARTMENTS -> RENAME TO DEPTS; |
+-------------------------------------+ | Tables_in_employeedb | +-------------------------------------+ | co_detail | | depts | | employee | +-------------------------------------+