- 5 years ago
- Zaid Bin Khalid
- 4,210 Views
-
5
In this session, you will learn how to change an existing table by using SQL.
SQL ALTER TABLE Statement.
The ALTER statement is used to add, delete, or change columns in the current table.
In other words, we can say that The ALTER TABLE statement is also used to add and drop many huddles in an existing table.
Modifying Existing Tables.
This is quite possible after creating a table when you start using it. You will know that you have forgotten to mention any column, constraint, or specified a wrong name for the column.
In such types of problems, you can use the ALTER statement to change an existing table by using adding, renaming, modifying, or deleting statements in a column.
Let’s assume we have a table which is called the shipper’s table in our SQL database, the structure of this database is shown as:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
We will use this shipper’s table for all of our ALTER TABLE statements.
Adding a New Column.
The basic syntax to add a column in a table is shown below.
ALTER TABLE table_name ADD column_name data_type constraints;
The mentioned statement will add a new column in the shipper’s table.
ALTER TABLE shippers ADD fax VARCHAR(20);
After executing the above statement, you will see the table texture using the command DESCRIBE shippers; the output result will look as follow.
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(60) | NO | | NULL | |
| fax | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
Changing Column Position.
The basic syntax to Change a column in a table is shown below.
ALTER TABLE table_name
MODIFY column_name column_definition AFTER column_name;
The following statement places the column fax after the shipper_name column in the table.
mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;
Adding Constraints.
In our shipper’s table, there is one main problem. If you add records with duplicate phone numbers, it will not give you an error notification, which is not good, it should be different or unique.
You can fix this by adding a constraint UNIQUE in the phone column.The basic syntax for adding this constraint into the existing table column is shown as.
ALTER TABLE table_name ADD UNIQUE (column_name,...);
Removing Columns.
The basic syntax to REMOVE a column in a table is shown below.
ALTER TABLE table_name DROP COLUMN column_name;
The mentioned statement removes our added column from the shipper’s table.
mysql> ALTER TABLE shippers DROP COLUMN fax;
After executing the above statement, you will see the table texture, and it looks like as shown.
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int | NO | PRI | NULL | auto_increment |
| shipper_name | varchar(60) | NO | | NULL | |
| phone | varchar(20) | NO | UNI | NULL | |
+--------------+-------------+------+-----+---------+----------------+
Changing Data Type of a Column.
You can also change or replace the data type of a column in SQL Server by using the ALTER clause, as shown.
ALTER TABLE table_name ALTER COLUMN column_name new_data_type;
MySQL database server does not support the ALTER COLUMN syntax.It helps an alternate modify clause that you can use to alter the column, as shown.
ALTER TABLE table_name MODIFY column_name new_data_type;
Renaming Tables.
The basic syntax to RENAMING a table is shown below.
ALTER TABLE current_table_name RENAME new_column_name;
The following statement renames our shipper’s table shipper.
mysql> ALTER TABLE shippers RENAME shipper;
You can also change or replace the same thing in MySQL by using the RENAME TABLE statement, as shown:
mysql> RENAME TABLE shippers TO shipper;
- 5 years ago
- Zaid Bin Khalid
- 4,210 Views
-
5