SQL Alter Table, SQL Tutorial, Global Guide Line Technology.
Bookmark Bookmark: SQL Alter Table, SQL Tutorial Forum | Blog |

      Home                    

Basic SQL Guide.           
SQL Introduction.               
 What is Table?                  
 SQL Create Table.            
 SQL Insert Statement.      
 SQL Select Keyword.        
 SQL Order by clause.        
 SQL Count Statement.      
 SQL Group By Clause.      
 SQL Having Clause.          
 SQL Alias Tables.             
 SQL Join Tables.              
 SQL Outer Join.               
 SQL Update Statement.   
 SQL Delete Statement.    
 SQL Distinct Keyword.      
 SQL Where Keyword.       
 SQL And Or Keyword.      
 SQL In Keyword.              
 SQL Between Keyword.   
 SQL Like Keyword.          
 SQL Functions.                
 SQL Concatenate.           
 SQL Substrings.               
 SQL Trim Function.         
 SQL Constraints.              
 SQL Primary Key.            
 SQL Foreign Key.            
 SQL Create Views.           
 SQL Create Index.           
 SQL Alter Table.
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                

SQL Tutorial >> SQL Alter Table.


     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     
 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]

Copyright 2005 -  2018 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 250 visitors are online now