MySQL Alter Table

You can alter a table and change a column data type or add new columns, as well as other changes. The alter command will perform the action to add a new column. The syntax is:

ALTER TABLE table_name ADD COLUMN column_name data_type modifiers;

Example - Primary Key

A Primary Key identifies each column. Each value in that column must be unique, and the column must not contain null values or duplicated values. The Primary Key is typically applied to the ID column as this will always be unique, and the value cannot be used again.

In the CLC type the following:

ALTER TABLE Games ADD PRIMARY KEY (Id);

If you now type EXPLAIN Games you should see the ID column has Primary Key as a constraint and does not allow null values.

Add Modifiers

Modifiers are extra information you can add to a column; for example, a column may not contain null values. There are some common modifiers:

Modifer Description
NOT NULL Specifies that the column may not contain null values
UNIQUE Specifies that the column may contain unique values only
AUTO_INCREMENT Specifies that column will automatically increment one number from the previous column (only for numeric columns)
Primary Key Specifies that the column should be used as a Primary Key, used to identify each row in that table

Consoles Table

We are now going to create a new table called Consoles which will store the console name, manufacturer and release date. We will also add modifiers to each column.

Open Notepad++ and type the following:

CREATE TABLE IF NOT EXISTS Consoles 

(

Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
cName VARCHAR (100) NOT NULL,
cManufacturer VARCHAR (100) NOT NULL,
cYear YEAR NOT NULL

);

Save this file as Consoles.sql and execute the file using the Source command through the CLC. Next, use the Explain command to see the columns of the Consoles table. You should see that the modifiers have been applied: NOT NULL has been applied to all the columns and the cName and cManufacturer only accept text within 100 characters.

Delete Columns

You can use the alter command to delete columns. In a table you use the DROP COLUMN command to delete the column.

ALTER TABLE table_name DROP COLUMN column_name;

Summary

This tutorial outlined altering database tables, how to add modifiers to tables, and how to delete columns as well as add new columns. Remember the common modifers are UNIQUE, Primary Key, NOT NULL and AUTO_INCREMENT.