Back
Next
Once a table is created in the database,
there are many occasions where we may wish
to change the structure of our created table. Typical
cases include the following:
►- Add a column to existing table
►- Drop a column from an existing table
►- Change a column name of any table
►- Change the data type for a column in a
table
►- Add Constraints such as Not Null, Primary
Key, Foreign Key etc in a table
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 and much more.
The SQL syntax for ALTER TABLE is
Syntax for SQL Alter Table Command
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 experiment through examples for each one of the above,
using the "Employees" table created in the CREATE TABLE section
of our SQL Tutorial:
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Address char(50),
City char(50),
Country char(25),
DOB date)
First, we want to add a column called "Gender" to this table. To do this, we
will use the below query.
Example of SQL Alter Table
ALTER table Employees
add Gender char(1)
Now the Employees table altered and new column has been added with the name of "Gender"
Table Employees new structure:
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Address char(50),
City char(50),
Country char(25),
DOB date
Gender char(1))
Example of SQL Alter Table With Chang Keyword
ALTER table Employees
change Address Addr char(70)
Now the Employees table altered and column "Address" is changed with "Addr"
column name
Table Employees new structure:
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Addr char(70),
City char(50),
Country char(25),
DOB date
Gender char(1))
Example of SQL Alter Table With Modify Keyword
ALTER table Employees
modify Addr char(60)
Now the Employees table altered and column "Addr" data type has been modified
from 70 characters to 60 characters.
Table Employees new structure:
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Addr char(60),
City char(50),
Country char(25),
DOB date
Gender char(1))
Example of SQL Alter Table With Drop Keyword
ALTER table Employees
drop Gender
Now the Employees table altered and Column "Gender" has been dropped form the
table Employees.
Table Employees new structure:
TABLE Employees
(FirstName char(50),
LastName char(50),
Email char(25),
Address char(50),
City char(50),
Country char(25),
DOB date)
Back
Next
| 
Home 