Scribd
Upload
80 views2 pages

MySQL ALTER TABLE Guide

The ALTER TABLE statement in MySQL is used to modify the structure of existing tables, allowing you to add columns, drop columns, change column data types, add or drop constraints like primary keys, rename tables, and set auto-increment attributes on columns. The document provides examples of SQL syntax for common ALTER TABLE operations like adding a column, adding a constraint, changing a column, dropping a column, dropping a constraint, and renaming a table.

Uploaded by

Priyanka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
80 views2 pages

MySQL ALTER TABLE Guide

The ALTER TABLE statement in MySQL is used to modify the structure of existing tables, allowing you to add columns, drop columns, change column data types, add or drop constraints like primary keys, rename tables, and set auto-increment attributes on columns. The document provides examples of SQL syntax for common ALTER TABLE operations like adding a column, adding a constraint, changing a column, dropping a column, dropping a constraint, and renaming a table.

Uploaded by

Priyanka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

 ALTER Command – You use the ALTER TABLE  

statement to change the structure of existing


tables. The ALTER TABLE statement allows you to add a column, drop a column, change the
data type of column, add primary key, rename table, and many more. The following illustrates the
ALTER TABLE  statement syntax:

ALTER TABLE table_name action1[,action2,…]

Using MySQL ALTER TABLE statement to add a new column into a table

ALTER TABLE table_name ADD COLUMN column_name datatype constraints


AFTER column_name;

Using MySQL ALTER TABLE statement to add a constraint to a table

ALTER TABLE tablename


ADD [CONSTRAINT constraint_name] constraint_type(column, …),

Changing columns using MySQL ALTER TABLE statement: Using MySQL ALTER
TABLE statement to set auto-increment attribute for a column.

ALTER TABLE table_name CHANGE COLUMN old_column_name new_coulumn_name datatype


constraint AUTO_INCREMENT;

Using MySQL ALTER TABLE to drop a column from a table

ALTER TABLE table_name DROP COLUMN column_name;


Using MySQL ALTER TABLE to drop a constraint from a table: Please note you can drop
foreign key constraints. You can use the following syntax to find the constraint name:

SHOW CREATE TABLE table_name;

ALTER TABLE table_name DROP <constraint_name> <user_ defined_constraint_name>;

Renaming table using MySQL ALTER TABLE statement

ALTER TABLE table_name RENAME TO new_table_name;

We can use the CHECK TABLE statement to check the status of the renames table, following is
the syntax:

CHECK TABLE database_object_name;

You might also like