Friday, 22 June 2018

ALTER TABLE

This command is used to Add/Change/Modify/Drop existing structure of the table.

ADD Column
Enable/Disable Constraints
Change Column
Modify Column
Drop Column

ADD Column :- When a new column is to be added to the table structure without constraints.

Syntax :-

ALTER TABLE table_name
ADD COLUMN column_name datatype (size);

Example:-

ALTER TABLE my_tab
ADD COLUMN stu_id integer (5);

Change Column :-This is used to change name and data type of an existing column without constraints.

Syntax:-

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type (size);

Example:-

ALTER TABLE my_tab
CHANGE COLUMN name student varchar (5);

Modify Column :- This is used to modify size of the data type or the data type itself of an existing column without changing column name.

Syntax:-

ALTER TABLE table_name
MODIFY COLUMN column_name datatype (size);

Example:-

ALTER TABLE  my_tab
MODIFY COLUMN roll integer (10);

DROP COLUMN :- When a column in a table need to delete

Syntax :- 

ALTER TABLE table_name
DROP COLUMN column_name;

Example:-

ALTER TABLE my_tab
DROP COLUMN roll;

When removing constraints from a column

Syntax:-

ALTER TABLE table_name
DROP constraints_name column_name;

Example:-

ALTER TABLE my_tab
DROP UNIQUE KEY (roll);


0 Comments:

Post a Comment

Popular Posts

Categories

Android (21) AngularJS (1) Books (3) C (75) C++ (81) Data Strucures (4) Engineering (13) FPL (17) HTML&CSS (38) IS (25) Java (85) PHP (20) Python (83) R (68) Selenium Webdriver (2) Software (13) SQL (27)