MySQL - ALTER TABLE Statement

Table of content

MySQL ALTER TABLE Statement

The Alter TABLE statement of MySQL helps to perform changes on a table. Using this statement, we can add, delete, or modify columns in an existing table and we can also rename it.

Syntax

Following is the syntax of the ALTER Table statement −

ALTER TABLE table_name [alter_option ...];

Where, table_name is the name of the existing table you need to change and the alter_option represents the change you need to perform on the table such as ADD COLUMNS, DROP COLUMN etc.

Adding a new column

For instance, if you need to add a new column to a table you should use the ADD COLUMN option as shown below −

ALTER TABLE table_name ADD COLUMN (column_name column_definition...)

Example

Suppose we have created a table as shown below −

CREATE TABLE Employee(
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255)
);

Following query adds a new column named phone to the Employee table −

ALTER TABLE Employee ADD COLUMN (Phone int (10));

To verify the above query if you describe the table using the DESC command you can observe the created column in the field list as shown below −

DESC employee;

Following is the output of the above mysql query −

Field Type Null Key Default Extra
Name varchar(255) YES NULL
Salary int No NULL
Location varchar(255) YES NULL
Phone int YES NULL

Adding a column after an existing column

If you want to add a column before or after an existing column you need to use AFTER followed by the name of the existing column (after the column definition).

ALTER TABLE table_name ADD COLUMN (column_name column_definition...)

Example

Following query adds a column named address after the Location.

ALTER TABLE Employee ADD COLUMN Address VARCHAR(50) AFTER Location;

To verify the above query if you describe the table using the DESC command you can observe the created column in the field list as shown below −

DESC employee;

Output

The above query produces the following output &mius;

Field Type Null Key Default Extra
Name varchar(255) YES NULL
Salary int NO NULL
Location varchar(255) YES NULL
Address varchar(50) YES NULL
Phone int YES NULL

Adding a column at start

You can add the new column at the start using the FIRST statement along with alter, following is the syntax to do so −

ALTER TABLE table_name ADD COLUMN (column_name column_definition...)

Example

Following query adds a column with name ID at start −

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

To verify the above query if you describe the table using the DESC command you can observe the created column in the field list as shown below −

desc employee;

Output

Following is the output of the above query −

Field Type Null Key Default Extra
ID int YES NULL
Name varchar(255) YES NULL
Salary int NO NULL
Location varchar(255) YES NULL
Address int YES NULL
Phone int YES NULL

Dropping a column

You can drop an existing column by using the DROP statement along with ALTER following is the syntax to do so −

ALTER TABLE table_name DROP COLUMN column_name;

Example

First of all, let us verify the definition of the table using DESC statement −

desc employee;

Output

The above query produces the following output −

Field Type Null Key Default Extra
ID int YES NULL
Name varchar(255) YES NULL
Salary int NO NULL
Location varchar(255) YES NULL
Address varchar(50) YES NULL
Phone int YES NULL

Following query drops the column named ID −

ALTER TABLE Employee DROP COLUMN ID;

To verify the above query if you describe the table using the DESC command you can observe the name of the deleted column is missing from the field list −

desc employee;

Following is the output of the above query −

Field Type Null Key Default Extra
Name varchar(255) YES NULL
Salary int NO NULL
Location varchar(255) YES NULL
Address varchar(50) YES NULL
Phone int YES NULL

Adding an index

You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −

ALTER TABLE table_name ADD INDEX index_name [index_type] (key_part,...)

Example

Following query adds an index on the columns Name and Salary −

ALTER TABLE Employee ADD INDEX sample_index (Name, Salary);

You can verify the indices of the table using the SHOW INDEX statement as shown below −

SHOW INDEX FROM Employee;

Output

The above query will produce the following output −

*************** 1. row ***************
        Table: employee
   Non_unique: 1
     Key_name: sample_index
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************** 2. row ***************
        Table: employee
   Non_unique: 1
     Key_name: sample_index
 Seq_in_index: 2
  Column_name: Salary
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

Adding a primary key

Following is the syntax of creating a primary key −

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey 
PRIMARY KEY (column1, column2...);

Example

Let us create a column named ID in the table Employee −

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

Following query creates a primary key on the column ID −

ALTER TABLE Employee ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID);

To verify the above query if you describe the table using the DESC command you can observe PRI against the ID field under the column Key −

desc employee;

Output

Following is the output of the above program −

Field Type Null Key Default Extra
ID int NO PRI NULL
Name varchar(255) YES MUL NULL
Salary int NO NULL
Location varchar(255) YES NULL
Phone int YES NULL

Adding a foreign key

Following is the syntax of creating a foreign key −

ALTER TABLE table_name ADD CONSTRAINT key FORIEGN KEY (column_name);

Example

Assume we have created another table named test as shown below −

CREATE table Test (ID int PRIMARY KEY);

Let us create a column named ID in the table Employee −

ALTER TABLE Employee ADD COLUMN ID INT FIRST;

Following query creates a primary key on the column ID −

ALTER TABLE Employee ADD CONSTRAINT fk FOREIGN KEY(ID) REFERENCES test(ID);

Adding a constraint

Following is the syntax to add constraint to a column −

ALTER TABLE table_name ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2...);

Example

Following query adds UNIQUE constraint to the table Employee −

ALTER TABLE Employee ADD CONSTRAINT con UNIQUE(Phone);

Dropping an index

Following is the syntax to drop an index on a table −

ALTER TABLE table_name DROP INDEX index_name

Example

Following query drops the index created above on the table Employee −

ALTER TABLE Employee DROP INDEX sample_index;

Drop a primary key

Following is the syntax to drop a primary key −

ALTER TABLE table_name DROP PRIMARY key_name;

Example

Suppose we have created a table and added a PRIMARY KEY as shown below −

create table sample (ID INT);
alter table sample add CONSTRAINT PRIMARY KEY (ID);

To verify the above query if you describe the table using the DESC command you can observe PRI against the ID field under the column Key −

desc sample;

Output

Following is the output of the above query −

Field Type Null Key Default Extra
ID int NO PRI NULL

Following query drops the above created Primary key −

alter table sample drop PRIMARY KEY;

If you describe the sample table using the desc statement you can observe that the primary constraint is removed −

desc sample;

Output

The above query produces the following output −

Field Type Null Key Default Extra
ID int NO NULL

Dropping a foreign key

Following is the syntax to drop a foreign key −

ALTER TABLE table_name DROP PRIMARY key_name;

Example

Following query drops the foreign key fk of the table employee −

ALTER TABLE Employee DROP FOREIGN KEY fk;
Advertisements