This Articles Contents
Alterations
In this article we discuss how to modify various database structures once they are created.
MySQL allows us to change our minds about the entities we create and alter them. We can rename tables, add, remove, or rename columns, change the type of an existing column, etc.
Example Syntax :
ALTER TABLE table
CHANGE
oldColumnName
newColumnName
<datatype>
<restrictions>;-- The article queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);
-- Query 2
ALTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous";
-- Query 3
ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous";
-- Query 4
ALTER TABLE Actors MODIFY First_Name INT;
-- Query 5
ALTER TABLE Actors MODIFY First_Name varchar(300);
-- Query 6
ALTER TABLE Actors ADD MiddleName varchar(100);
-- Query 7
ALTER TABLE Actors DROP MiddleName;
-- Query 8
ALTER TABLE Actors ADD MiddleName varchar(100) FIRST;
-- Query 9
ALTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB;
--Query 10
ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);ALTER TABLE tablename CHANGE column1 column2 varchar(120);
Let’s say we want to rename the column FirstName to First_Name for the Actors table. We can do so as follows:
ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);
ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);We not only change the column name, but we also change the column length from 20 to 120. If we only wanted to rename the column, we would still need to re-specify the type of the column as well as any other clauses that were specified the first time.
ALTER TABLE tablename MODIFY columnname varchar(20) DEFAULT “Anonymous”;
We can use the MODIFY keyword if we wish to alter the type or the clauses for a column. For instance, we can specify the default value for the column First_Name to be the string “Anonymous” as follows:
ALTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous";
We can also use the CHANGE statement but that will require us to specify the same column name twice as we aren’t renaming the column.
ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous";ALTER TABLE table MODIFY column INT;
We have to be cautious when trying to change the type of an existing column. For instance, if we try to change the first name column from type varchar to int, we’ll run into an error (as shown below) because the conversion is nonsensical.
ALTER TABLE Actors MODIFY First_Name INT;
On the contrary, we can easily convert the type of a column that doesn’t result in data loss. For instance, we can change the column first name to have a varchar length of 300 as shown below:
ALTER TABLE Actors MODIFY First_Name varchar(300);
ALTER TABLE tablename ADD column varchar(100);
We can also add a column to an existing table. We can add a new column MiddleName to the Actors table using the following query:
ALTER TABLE Actors ADD MiddleName varchar(100);
ALTER TABLE tablename DROP column;
We can also remove the newly added column using the DROP statement as follows:
ALTER TABLE Actors DROP MiddleName;
ALTER TABLE tablename ADD column varchar(100) FIRST;
We can also control the position of the new column within the table using the FIRST or AFTER keyword. The following statement adds the middle name as the first column:
ALTER TABLE Actors ADD MiddleName varchar(100) FIRST;
ALTER TABLE tablename ADD column varchar(100) AFTER DoB;
Now we’ll drop the middle name column and add it after the date of birth (DoB) column as follows:
ALTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB;
If an index is defined on a column, dropping the column also removes the index, if the index consists of only that one column.
ALTER TABLE tablename DROP column1, ADD column2 varchar(100);
We can combine several alterations in a single MySQL statement separated by comma. In fact, combining alterations is much more efficient as it avoids the cost of creating a new table, copying data from the old table to the new, dropping the old table, and renaming the old table to the new table for each alteration. In the example below, we drop the middle name column and recreate it using a slightly different column name, all in a single statement.
ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);
For some alter operations under the hood, MySQL creates a new table with the requested alter changes, copies the data from the old table to the new one, deletes the old table, and then renames the new table to Actors. An alter operation can be expensive if the table needs to be rebuilt.
Alter Index
In this lesson we discuss how to modify an index.
It is hard to predict what indexes to create without observing the access patterns for an application. We can add, remove, or modify indexes after the application is deployed. Note that modifying indexes doesn’t change the data in the table.
Example Syntax :
ALTER TABLE table
ADD INDEX indexName (col1, col2, ... coln);
-- Query 1
ALTER TABLE Actors ADD INDEX nameIndex (FirstName);
-- Query 2
ALTER TABLE Actors ADD INDEX nameIndexWithOnlyTenChars (FirstName(10));
-- Query 3
ALTER TABLE Actors DROP INDEX nameIndex;
-- Query 4
ALTER TABLE Actors DROP PRIMARY KEY;
-- Query 5
CREATE TABLE Movies (Name VARCHAR(100), Released DATE, PRIMARY KEY (Name));
DESC Movies;
ALTER TABLE Movies DROP PRIMARY KEY;
ALTER TABLE Movies ADD PRIMARY KEY (Released);ALTER TABLE tablename ADD INDEX nameIndex (column1);
MySQL allows us to add a new index to an existing table. Say we find out that a lot of users of our application are searching actors by the first name. We can speed up their queries by declaring an index on the first name column as follows:
ALTER TABLE Actors ADD INDEX nameIndex (FirstName);
If we want to create the index on the first name column but use only the first ten characters, the query would look like as follows:
ALTER TABLE Actors ADD INDEX nameIndexWithOnlyTenChars (FirstName(10));ALTER TABLE tablename DROP INDEX nameIndex;
We can also delete the index we just created as follows:
ALTER TABLE Actors DROP INDEX nameIndex;
ALTER TABLE tablename DROP PRIMARY KEY;
We can’t add a second primary key to a table that already has a primary key. However, we can drop the existing primary key and declare a new one on the table. In the case of the Actors table, we can’t drop the primary key ID as it is an auto_increment column and an auto_increment column must also be the primary key. Attempting to drop the ID column as the primary key results in the following error:
ALTER TABLE Actors DROP PRIMARY KEY;
As an example, we’ll create a temporary table Movies with just two columns, name and release date. Next, we’ll demonstrate how to delete the primary key and then declare the other column to be the primary key for the table.
- CREATE TABLE Movies (Name VARCHAR(100), Released DATE, PRIMARY
KEY (Name)); - DESC Movies;
- ALTER TABLE Movies DROP PRIMARY KEY;
- ALTER TABLE Movies ADD PRIMARY KEY (Released);
It is hard to predict what indexes to create without observing the access patterns for an application. We can add, remove, or modify indexes after the application is deployed. Note that modifying indexes doesn’t change the data in the table.
Example Syntax :
ALTER TABLE oldTableName
RENAME newTableName;-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
ALTER TABLE Actors RENAME ActorsTable;
-- Query 2
DROP TABLE IF EXISTS ActorsTable;
-- Query 3
DROP TABLE IF EXISTS Table1, Table2, Table3;
-- Query 4
DROP DATABASE IF EXISTS MovieIndustry;ALTER TABLE tablename1 RENAME tablename2;
We can also rename a table after creating it. In the snippet below, we rename the Actors table to ActorsTable as follows:
ALTER TABLE Actors RENAME ActorsTable;
DROP TABLE IF EXISTS tablename;
We have already discussed dropping tables, but we can use a slightly improved query using the IF EXISTS clause.
DROP TABLE IF EXISTS ActorsTable;Executing the above query multiple times will not result in an error once the table is already deleted. The IF EXISTS clause is useful when deletion occurs in an automated script. The script continues to execute even if the table has previously been deleted.
DROP TABLE IF EXISTS Table1, Table2, Table3;
We can delete multiple tables in a single statement by specifying a comma-separated list of table names to delete.
DROP TABLE IF EXISTS Actors, Employees, Students;DROP DATABASE IF EXISTS DatabaseName;
We can drop the entire database as follows:
DROP DATABASE IF EXISTS MovieIndustry;