ALTER table command mysql sql database manipulation adding new column drop column index consrtaint
Definition And Explaination:-
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create indexes, change the type of existing columns, or rename columns or the table itself.You can use ALTER TABLE to modify the structure of a table that has not been added to a database.Alter table command allows
* To add the field;
* To delete the field out;
* To change the DEFAULT value for any of the field;
* To add or delete the table PRIMARY KEY;
* To add or delete the table FOREIGN KEY;
* To add or delete UNIQUE constraint;
* To add or delete CHECK constraint;
* To rename fields or table itself;
* To change field type.
Syntax And Structure:-
ADD [ COLUMN ] column_definition
| ADD [ COLUMN ] ( column_definition [, column_definition ... ] )
| ADD table_constraint_definition
| ADD ( table_constraint_definition [, table_constraint_definition ... ] )
| ALTER [ COLUMN ] column_name < SET DEFAULT default_option | DROP DEFAULT>
| DROP [ COLUMN ] column_name [ drop_behavior ]
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP CONSTRAINT IDENT
| CHANGE [ COLUMN ] old_col_name column_definition
| MODIFY [ COLUMN ] column_definition
| RENAME [ AS ] table_name
Examples:-
ALTER TABLE customers ADD CONTACT_PHONE VARCHAR(30)
ALTER TABLE customers ADD COLUMN CONTACT_PHONE VARCHAR(30)
ALTER TABLE customers DROP contact_name
ALTER TABLE Person CHANGE LastName Surnname String ( 40 )
ALTER TABLE Person MODIFY Surnname String ( 30 )
ALTER TABLE offices ADD CONSTRAINT inregion FOREIGN KEY ( region ) REFERENCES regions
ALTER TABLE salesreps DROP CONSTRAINT worksin
ALTER TABLE Customer ADD COLUMN Fax2 c(20) NOT NULL
