MySQL - ALTER Table - MySQL - dyclassroom

In this tutorial we will learn to alter tables in MySQL.

We use the ALTER TABLE table_name command to alter tables in MySQL. And in this tutorial we will cover some of the commonly used modification that we will encounter.

Add new column
Modify column
Rename column
Rename and modify column
Drop column
Add unique key
Drop unique key
Add index
Drop index
Add foreign key
Remove foreign key

Add new column

In the following example we are adding birthday column to the employee table that we created in the CREATE Table tutorial.

This is how our table looks now.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Now we will add the birthday column after score column.

ALTER TABLE `employee`
ADD COLUMN `birthday` DATE DEFAULT NULL
AFTER `score`;

Now our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE DEFAULT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Modify column

Lets modify the birthday column that we added to the employee table by changing its type from DATE to DATETIME.

ALTER TABLE `employee`
MODIFY COLUMN `birthday` DATETIME DEFAULT NULL;

Now our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATETIME DEFAULT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Rename column

Lets rename the birthday column to dateofbirth.

ALTER TABLE `employee`
CHANGE COLUMN `birthday` `dateofbirth` DATETIME DEFAULT NULL;

Now our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `dateofbirth` DATETIME DEFAULT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Rename and modify column

Lets rename the dateofbirth column back to birthday and modify the column to NOT NULL and type from DATETIME to DATE.

ALTER TABLE `employee`
CHANGE COLUMN `dateofbirth` `birthday` DATE NOT NULL;

Now our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Drop column

To drop or delete a column from a table we use the DROP COLUMN command.

In the following example we are dropping the temp column from the employee table.

Right now the table looks like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE NOT NULL,
    `temp` INT(11) DEFAULT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Now we will drop the temp column from the table.

ALTER TABLE `employee`
DROP COLUMN `temp`;

Now the table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Add unique key

We add the UNIQUE KEY constraint to ensure that the column contains distinct values.

In the following example we are adding a new column email to the employee table.

ALTER TABLE `employee`
ADD COLUMN `email` VARCHAR(255) NOT NULL
AFTER `lastname`;

And now we are making it unique so that no two employee gets the same email address.

ALTER TABLE `employee`
ADD UNIQUE KEY `email_employee_UNIQUE` (`email`);

Now, our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`),
    UNIQUE KEY `email_employee_UNIQUE` (`email`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Drop unique key

To drop or delete a unique key constraint we use the DROP INDEX command.

For example, if we want to drop the UNIQUE KEY constraint from the email column by the symbol email_employee_UNIQUE we will run the following command.

ALTER TABLE `employee`
DROP INDEX `email_employee_UNIQUE`;

Add index

We add index to columns to optimise search. In the following example we are adding an index to the firstname column.

ALTER TABLE `employee`
ADD INDEX `firstname_employee_INDEX` (`firstname`);

So, our table will look like the following.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `score` INT(11) DEFAULT '0',
    `birthday` DATE NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`),
    UNIQUE KEY `email_employee_UNIQUE` (`email`),
    KEY `firstname_employee_INDEX` (`firstname`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Drop index

To drop or delete an index from a column we use the DROP INDEX command and the index symbol.

In the following example we are dropping the firstname_employee_INDEX index from the firstname column.

ALTER TABLE `employee`
DROP INDEX `firstname_employee_INDEX`;

Add foreign key

To add a foreign key to a table we use the ADD CONSTRAINT ... FOREIGN KEY command.

In the CREATE Table tutorial we created the comments table which looks like the following at the moment.

CREATE TABLE `comments` (
    `commentid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `commentbody` VARCHAR(200) NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`commentid`),
    UNIQUE KEY `commentid_comments_UNIQUE` (`commentid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

Now, lets add a new column employeeid to the comments table after the commentid column. The employeeid will be the foreign key and it will refer to the employeeid column of the employee table.

Adding the new column employeeid after columnid.

ALTER TABLE `comments`
ADD COLUMN `employeeid` VARCHAR(20) NOT NULL
AFTER `commentid`;

The foreign key column detail must match the column detail of the table it is referring to.

In the above example the foreign key column detail `employeeid` VARCHAR(20) NOT NULL is same as the employeeid column detail of the employee table.

Now we will add the foreign key constraint.

ALTER TABLE `comments`
ADD CONSTRAINT `employeeid_comments_FK` FOREIGN KEY (`employeeid`)
REFERENCES `employee` (`employeeid`)
ON DELETE CASCADE ON UPDATE CASCADE;

In the above command employeeid_comments_FK is used to create and INDEX for the employeeid column.

The foreign key in the comments table is the employeeid column. This column refers to the employeeid column of the employee table.

We also have ON DELETE CASCADE ON UPDATE CASCADE which means if the employeeid in the employee table is updated or deleted then it will be reflected to the comments table.

Now, our comments table will look like the following.

CREATE TABLE `comments` (
    `commentid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `employeeid` VARCHAR(20) NOT NULL,
    `commentbody` VARCHAR(200) NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`commentid`),
    UNIQUE KEY `commentid_comments_UNIQUE` (`commentid`),
    KEY `employeeid_comments_FK` (`employeeid`),
    CONSTRAINT `employeeid_comments_FK` FOREIGN KEY (`employeeid`)
        REFERENCES `employee` (`employeeid`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

So, on running the foreign key command a KEY employeeid_comments_FK was added for the column employeeid. This will help in indexing and during search operation.

Drop foreign key

To drop or delete a foreign key we use the DROP FOREIGN KEY command.

For example, if we want to drop the foreign key constraint employeeid_comments_FK from the comments table we will run the following command.

ALTER TABLE `comments`
DROP FOREIGN KEY `employeeid_comments_FK`;

And to drop the index key employeeid_comments_FK that was created when the foreign key was added we use the following command.

ALTER TABLE `comments`
DROP INDEX `employeeid_comments_FK`;