SQL: ALTER
command
alter
command is used for altering the table structure, such as,
- to add a column to existing table
- to rename any existing column
- to change datatype of any column or to modify its size.
- to drop a column from the table.
ALTER
Command: Add a new Column
Using ALTER
command we can add a column to any existing table. Following is the syntax,
ALTER TABLE table_name ADD(
column_name datatype);
Here is an Example for this,
ALTER TABLE student ADD(
address VARCHAR(200)
);
The above command will add a new column address
to the table student, which will hold data of type varchar
which is nothing but string, of length 200.
ALTER
Command: Add multiple new Columns
Using ALTER
command we can even add multiple new columns to any existing table. Following is the syntax,
ALTER TABLE table_name ADD(
column_name1 datatype1,
column-name2 datatype2,
column-name3 datatype3);
Here is an Example for this,
ALTER TABLE student ADD(
father_name VARCHAR(60),
mother_name VARCHAR(60),
dob DATE);
The above command will add three new columns to the student table
ALTER
Command: Add Column with default value
ALTER
command can add a new column to an existing table with a default value too. The default value is used when no value is inserted in the column. Following is the syntax,
ALTER TABLE table_name ADD(
column-name1 datatype1 DEFAULT some_value
);
Here is an Example for this,
ALTER TABLE student ADD(
dob DATE DEFAULT '01-Jan-99'
);
The above command will add a new column with a preset default value to the table student.
ALTER
Command: Modify an existing Column
ALTER
command can also be used to modify data type of any existing column. Following is the syntax,
ALTER TABLE table_name modify(
column_name datatype
);
Here is an Example for this,
ALTER TABLE student MODIFY(
address varchar(300));
Remember we added a new column address
in the beginning? The above command will modify the address
column of the student table, to now hold upto 300 characters.
ALTER
Command: Rename a Column
Using ALTER
command you can rename an existing column. Following is the syntax,
ALTER TABLE table_name RENAME
old_column_name TO new_column_name;
Here is an example for this,
ALTER TABLE student RENAME
address TO location;
The above command will rename address
column to location
.
ALTER
Command: Drop a Column
ALTER
command can also be used to drop or remove columns. Following is the syntax,
ALTER TABLE table_name DROP(
column_name);
Here is an example for this,
ALTER TABLE student DROP(
address);
The above command will drop the address
column from the table student.