MySQL Alter Table Statement

After you create a database and add your tables to you realize that you forgot to add a field in one of the tables. This is one situation. Other situations are that a column is declared with wrong data type, a primary key is not added, or a constraint is wrongly defined in a table. MySQL Alter Table Statement allows a developer to change the structure or definition of existing tables.

Uses of MySQL Alter Table Statement

The MySQL Alter Table Statement can be used to do any of the following activities on tables:

  • Add new column(s)
  • Drop an existing column
  • Modify name/ data-type/ length of an existing column
  • Add Primary Key to an existing table
  • Add Foreign Key to an existing column of a table
  • Add Default value for a column in an existing table
  • Add Check Constraint for a column in an existing table
  • Add  Unique Constraint for  a column in an existing table
  • Drop Primary Key Constraint of a table
  • Drop Foreign Key Constraint of a column in an existing table
  • Drop Check Constraint of a column in an existing table
  • Drop Unique Constraint of a column in an existing table

Syntax and Examples

To Add Column

MySQL Alter Table statement can be used to add new columns in an existing table. One or more columns can be added in one statement. One column can be added after already existing column of the table or as the first column of the table. You will use the keywords FIRST and AFTER for this.

ALTER TABLE Table-name ADD [COLUMN] column_name data-type   [FIRST | AFTER column_name]

ALTER TABLE ADD COLUMN genre VARCHAR(15);
MySQL Alter Table Add Column
ALTER TABLE artists ADD COLUMN country VARCHAR(25) AFTER artist_name;
Add column after
ALTER TABLE artists ADD COLUMN ratings INT FIRST;
add columns at first

To Add Columns

ALTER TABLE Table-name ADD [COLUMN] (column_name datatype,…)

ALTER TABLE artists ADD (dob DATE, age INT);
Add Columns

To Add Primary Key Constraint

ALTER TABLE Table-name ADD  [CONSTRAINT] constraint_name PRIMARY KEY (column_name)

ALTER TABLE artists ADD CONSTRAINT pk_art_id PRIMARY KEY (artist_id);
Add Primary key

To Add Foreign Key Constraint

ALTER TABLE Table-name ADD [CONSTRAINT constraint_name FOREIGN KEY  (col_name,…)        REFERENCES Table_name(column_name)

ALTER TABLE artists ADD CONSTRAINT fk_band_id FOREIGN KEY (band_id)  REFERENCES bands(band_id);
add foreign key

To Add Check Constraint

ALTER TABLE Table-name  ADD CONSTRAINT  check_name CHECK (expr)   

ALTER TABLE artists ADD CONSTRAINT ck_age CHECK (age>=18 and age<=50) ;

To Add Default Constraint

ALTER TABLE Table-name  ALTER [COLUMN] col_name {SET DEFAULT {literal | (expr)}

ALTER TABLE artists ALTER COLUMN genre SET DEFAULT('Disco')
;
MySQL Alter Table set default

To Drop Columns

ALTER TABLE Table-name  DROP [COLUMN] col_name

ALTER TABLE artists DROP COLUMN genre;
MySQL Alter Table drop column

Read about all the options of MySQL Alter Table Statement here.