MySQL
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.
ALTER TABLE table_name
In the following example we are adding birthday column to the employee table that we created in the CREATE Table tutorial.
birthday
employee
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.
score
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
Lets modify the birthday column that we added to the employee table by changing its type from DATE to DATETIME.
DATE
DATETIME
ALTER TABLE `employee` MODIFY COLUMN `birthday` DATETIME DEFAULT NULL;
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
Lets rename the birthday column to dateofbirth.
dateofbirth
ALTER TABLE `employee` CHANGE COLUMN `birthday` `dateofbirth` DATETIME DEFAULT NULL;
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
Lets rename the dateofbirth column back to birthday and modify the column to NOT NULL and type from DATETIME to DATE.
NOT NULL
ALTER TABLE `employee` CHANGE COLUMN `dateofbirth` `birthday` DATE NOT NULL;
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
To drop or delete a column from a table we use the DROP COLUMN command.
DROP COLUMN
In the following example we are dropping the temp column from the employee table.
temp
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.
We add the UNIQUE KEY constraint to ensure that the column contains distinct values.
UNIQUE KEY
In the following example we are adding a new column email to the employee table.
email
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
To drop or delete a unique key constraint we use the DROP INDEX command.
DROP INDEX
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.
email_employee_UNIQUE
ALTER TABLE `employee` DROP INDEX `email_employee_UNIQUE`;
We add index to columns to optimise search. In the following example we are adding an index to the firstname column.
firstname
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
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.
firstname_employee_INDEX
ALTER TABLE `employee` DROP INDEX `firstname_employee_INDEX`;
To add a foreign key to a table we use the ADD CONSTRAINT ... FOREIGN KEY command.
ADD CONSTRAINT ... FOREIGN KEY
In the CREATE Table tutorial we created the comments table which looks like the following at the moment.
comments
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.
employeeid
commentid
Adding the new column employeeid after columnid.
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.
`employeeid` VARCHAR(20) NOT NULL
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.
employeeid_comments_FK
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.
ON DELETE CASCADE ON UPDATE CASCADE
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.
To drop or delete a foreign key we use the DROP FOREIGN KEY command.
DROP FOREIGN KEY
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`;