Dropping and Altering Tables in MySQL

Implementing Database in MySQL – Part 7

Foreword: In this part of the series, we see how to alter and drop tables in the MySQL database.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 7 of my series, Implementing Database in MySQL. In this part of the series, we see how to alter and drop tables in the MySQL database. I assume you have read the previous parts of the series before reaching here, as this is a continuation.

We are still dealing with data definition. Remember data definition deals with creating of tables, altering tables, dropping tables, creating of indexes and giving different users, privileges to different tables. You should have seen how to create tables and give them indexes. In this part of the series we shall see how to alter and drop tables. You will see how to create user accounts (user name and password) and grant them certain permissions in the next part of the series.

Altering a Table
To create a table means to create the table structure (automatically saving the table in the database file). To alter a table, means to alter the table structure. It is not anybody who has the right to alter a table. You will be able to do that now because you are the database administrator (with userID that is root). A database administrator has the right to do anything in the database.

We shall start by looking at some examples, which alter tables. After that I will give you the complete syntax and explain how to read the complete syntax.

Example
We continue with the wholesale example.

Type the following commands in the command prompt to start the server, connect to it; enter the password, sql and select the database:

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

Let us add a Total column to the Orders table. Execute the following SQL statement:

ALTER TABLE Orders ADD Total DECIMAL(19,2);

A Total column is a computed value column. You normally do not have to include it; I have done so just for illustration.

Dropping a Table
In simple terms the SQL statement syntax to drop a table is:

    DROP TABLE table-name;

You will now create a table in the database and then drop it. Dropping a table means erasing the table from the database disk.

- Type and execute (press Enter for the last line) the following:

CREATE TABLE Purchase (
   purchase_num INTEGER NOT NULL PRIMARY KEY,
   date_purchased DATE,
   name CHAR(80)
);

You should now have a new table called Purchase.

- Now you will delete the table you have just created. Type the following and execute:

DROP TABLE Purchase;

The new table purchase should have been erased.

Let us now close the connection, and quit the tool. Type the following, pressing Enter after each command:

close
quit

Complete Syntax to Alter a Table
The complete syntax of the SQL Statement to alter a table is given below; glance through it and read the explanation below it:

ALTER TABLE Syntax

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION  {partition_names | ALL }
  | CHECK PARTITION  {partition_names | ALL }
  | OPTIMIZE PARTITION  {partition_names | ALL }
  | REBUILD PARTITION  {partition_names | ALL }
  | REPAIR PARTITION  {partition_names | ALL }
  | PARTITION BY partitioning_expression
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

Explaining the Syntax
I will just explain how I came about the following statement:

    ALTER TABLE Orders ADD Total DECIMAL(19,2);

I used this statement to add a new column.

The complete syntax is in sections. The lower you go down the syntax the more details you find about a word or phrase above in the syntax. In the syntax, | means and/or at that position; anything in square brackets is optional.

The first line in the syntax is:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

From this first line, I got

    ALTER TABLE Orders

for my own statement and I then proceeded to the details of, alter_specification, in the syntax. From the first line of the details of, alter_specification, I got

    ADD Total DECIMAL(19,2);

for my statement.

That is the kind of procedure you should follow to determine your own Altering SQL statements. You should apply the same reasoning to understand other syntaxes in MySQL, such as the CREATE TABLE syntax we saw in one of the previous parts of the series.

Time to take a break. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Dropping and Altering Tables in MySQL

Implementing Database in MySQL – Part 7

Introduction

Related Links

Comments