ALTER

ALTER Command: ADD 

• ADD command is used to add one or more new columns to the existing database tables
• The newly added columns will be empty and data can be entered by using insert command

Syntax 

ALTER TABLE table_name ADD(column_name datatype);

Adding a single column 

ALTER TABLE student ADD(address VARCHAR(100));

The above query will create a new column in the student table named address with varchar data type where  each value in the address column can hold a maximum length of 200 characters

Adding multiple columns 

Any number of columns can be added to the existing table by using add command separated with commas

ALTER TABLE student ADD (
father name VARCHAR(60),
mother name VARCHAR(60),
DOB DATE);

Adding constraints 

We can add constraints like a primary key, foreign key to the existing table anytime

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

Now the ID column will become the primary key of the table

Adding a column with a default value 

We can add a column to an existing table by applying the default clause

ALTER TABLE student ADD(
gender char(1) default 'M'
);

A new column will be created with the name gender of char data type where all the values in that column will be by default ‘M’ which can also be customized using insert command.

ALTER command: MODIFY 

By using the modify command, we can change the data type of the existing column or the size of the data type of the existing column

Syntax 

ALTER TABLE table_name MODIFY (
column_name datatype
);

Modifying a single column 

ALTER TABLE student MODIFY(
address varchar(75));

The above query changes the data type of the address column as varchar and size to 75 overriding the existing size value and data type

Modifying multiple columns

We can change data types or sizes of the columns of multiple columns at a time separating with commas

ALTER TABLE student MODIFY(
address varchar(75),dob date);

Note: 

• Modify can be used to change the data type of the existing column provided the new data type must be compatible with the existing column data if the existing column is empty then  it can change it to any data type i.e a column that is consisting of character data cannot be changed it to number data type
• The size of the existing data type of a column can be increased without any condition but the size is decreased so that new size should be sufficient to all values in the existing column i.e size cannot be decreased not less than the size accommodated by the largest value present in the column
• For example, if the city column contains Hyderabad consisting of nine characters now the size can be increased to any value but cannot be decreased less than 9.

Alter command: Rename 

Rename command in combination alter is used to rename the existing column name of a table

 Syntax

ALTER TABLE table_name RENAME
old_column_name TO new_column_name;

Example

ALTER TABLE student RENAME
address to location;

•  Column address has been renamed to location
•  Now all manipulations on this column must be performed using the new name location.

ALTER command: DROP

• Alter command is used to delete one or more existing columns present in the table
• We can also drop more than one column by separating the column names with commas

Syntax 

ALTER TABLE table_name DROP(
column_name);

Dropping a single column 

ALTER TABLE student DROP(
address);

Address column will be removed from the student table

Dropping multiple columns 

We can also drop more than one column by separating the column names with commas

ALTER TABLE student DROP(
   Caste, religion);

Note: 

In all databases we cannot drop all the columns using this drop command which means if there exists only one column in the table then drop command cannot be used to drop that column

Dropping constraints 

Drop command is used to remove the constraints imposed on the columns

ALTER TABLE emp
DROP CONSTRAINT PK_id;

For suppose ID is a primary key column of the table emp which is given a name of PK_id, upon dropping PK_id will remove the primary key of the table