Altering Tables Guide

Learn how to modify existing table structures using the ALTER TABLE statement, including adding columns, changing types, and managing indexes.

This guide provides essential instructions for modifying existing table structures. Learn how to add, drop, and change columns, manage indexes and default values, and rename tables, along with key precautions for these operations when working with your database.

Before You Begin: Backup Your Tables

Before making any structural changes to a table, especially if it contains data, always create a backup. The mariadb-dump utility is a common and effective tool for this.

Example: Backing up a single table

Suppose you have a database db1 and a table clients. Its initial structure is:

DESCRIBE clients;

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_id     | int(11)     |      | PRI | 0       |       |
| name        | varchar(25) | YES  |     | NULL    |       |
| address     | varchar(25) | YES  |     | NULL    |       |
| city        | varchar(25) | YES  |     | NULL    |       |
| state       | char(2)     | YES  |     | NULL    |       |
| zip         | varchar(10) | YES  |     | NULL    |       |
| client_type | varchar(4)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

To back up the clients table from the command-line:

mariadb-dump --user='your_username' --password='your_password' --add-locks db1 clients > clients.sql

• Replace 'your_username' and 'your_password' with your actual MariaDB credentials.

• --add-locks: Locks the table during the backup and unlocks it afterward.

• db1 clients: Specifies the database and then the table.

• > clients.sql: Redirects the output to a file named clients.sql.

Restoring from a backup

If you need to restore the table:

mariadb --user='your_username' --password='your_password' db1 < clients.sql

This command uses the mariadb client to execute the SQL in clients.sql, which will typically drop the existing table (if it exists) and recreate it from the backup. Ensure no critical data has been added to the live table since the backup if you intend to overwrite it.

For the examples that follow, we'll assume structural changes are being made, sometimes on an empty table for simplicity, but the backup step is always recommended for tables with data.

Adding Columns

Use the ALTER TABLE statement with the ADD COLUMN clause.

Add a column to the end of the table:

To add a status column with a fixed width of two characters:

ALTER TABLE clients
ADD COLUMN status CHAR(2);

Add a column after a specific existing column:

To add address2 (varchar 25) after the address column:

ALTER TABLE clients
ADD COLUMN address2 VARCHAR(25) AFTER address;

Add a column to the beginning of the table:

ALTER TABLE clients
ADD COLUMN new_first_column VARCHAR(50) FIRST;

(Assuming new_first_column is the one to be added at the beginning).

After additions, the table structure might look like (excluding new_first_column for consistency with original example flow):

DESCRIBE clients;

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_id     | int(11)     |      | PRI | 0       |       |
| name        | varchar(25) | YES  |     | NULL    |       |
| address     | varchar(25) | YES  |     | NULL    |       |
| address2    | varchar(25) | YES  |     | NULL    |       |
| city        | varchar(25) | YES  |     | NULL    |       |
| state       | char(2)     | YES  |     | NULL    |       |
| zip         | varchar(10) | YES  |     | NULL    |       |
| client_type | varchar(4)  | YES  |     | NULL    |       |
| status      | char(2)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Changing Column Definitions

Use ALTER TABLE with CHANGE or MODIFY.

Change column type (e.g., to ENUM):

The status column name is specified twice even if not changing the name itself when using CHANGE.

ALTER TABLE clients
CHANGE status status ENUM('AC','IA');

Change column name and keep type:

To change status to active while keeping the ENUM definition:

ALTER TABLE clients
CHANGE status active ENUM('AC','IA');

When using CHANGE, the current column name is followed by the new column name and the complete type definition.

Modify column type or attributes without renaming:

Use MODIFY if you are only changing the data type or attributes, not the name.

ALTER TABLE clients
MODIFY address1 VARCHAR(40); -- Assuming 'address1' is an existing column

Complex Changes (e.g., ENUM migration with data):

Changing ENUM values in a table with existing data requires careful steps to prevent data loss. This typically involves:

1. Temporarily modifying the ENUM to include both old and new values.

2. Updating existing rows to use the new values.

3. Modifying the ENUM again to remove the old values.

Example of changing address to address1 (40 chars) and preparing active ENUM for new values 'yes','no' from 'AC','IA':

ALTER TABLE clients
    CHANGE address address1 VARCHAR(40),
    MODIFY active ENUM('yes','no','AC','IA'); -- Temporarily include all

Then, update the data:

UPDATE clients
SET active = 'yes'
WHERE active = 'AC';

UPDATE clients
SET active = 'no'
WHERE active = 'IA';

Finally, restrict the ENUM to new values:

ALTER TABLE clients
MODIFY active ENUM('yes','no');

Dropping Columns

To remove a column and its data (this action is permanent and irreversible without a backup):

ALTER TABLE clients
DROP COLUMN client_type;

Managing Default Values

Set a default value for a column:

If most clients are in 'LA', set it as the default for the state column:

ALTER TABLE clients
ALTER state SET DEFAULT 'LA';

Remove a default value from a column:

This reverts the default to its standard (e.g., NULL if nullable, or determined by data type).

ALTER TABLE clients
ALTER state DROP DEFAULT;

This DROP DEFAULT does not delete existing data in the column.

Managing Indexes

Indexes are separate objects from columns. Modifying an indexed column often requires managing its index.

View existing indexes on a table:

The \G displays results in a vertical format, which can be easier to read for wide output.

SHOW INDEX FROM clients\G

Example output:

*************************** 1. row ***************************
           Table: clients
      Non_unique: 0
        Key_name: PRIMARY
    Seq_in_index: 1
     Column_name: cust_id
       Collation: A
     Cardinality: 0
        Sub_part: NULL
          Packed: NULL
         Comment:

Changing an indexed column (e.g., Primary Key):

Attempting to CHANGE a column that is part of a PRIMARY KEY without addressing the key might result in an error like "Multiple primary key defined". The index must be dropped first, then the column changed, and the key re-added.

ALTER TABLE clients
    DROP PRIMARY KEY,
    CHANGE cust_id client_id INT PRIMARY KEY;

The order is important: DROP PRIMARY KEY first.

Changing a column with another index type (e.g., UNIQUE):

If cust_id had a UNIQUE index named cust_id_unique_idx (Key_name from SHOW INDEX):

ALTER TABLE clients
    DROP INDEX cust_id_unique_idx, -- Use the actual Key_name
    CHANGE cust_id client_id INT UNIQUE;

If the Key_name is the same as the Column_name (e.g. for a single column UNIQUE key defined on cust_id where cust_id is also its Key_name):

ALTER TABLE clients
    DROP INDEX cust_id, -- If cust_id is the Key_name
    CHANGE cust_id client_id INT UNIQUE;

Changing index type and handling duplicates (e.g., INDEX to UNIQUE):

If changing from an index type that allows duplicates (like a plain INDEX) to one that doesn't (UNIQUE), and duplicate data exists, the operation will fail. To force the change and remove duplicates (use with extreme caution):

ALTER IGNORE TABLE clients
    DROP INDEX cust_id_idx, -- Assuming cust_id_idx is the name of the old INDEX
    CHANGE cust_id client_id INT UNIQUE;

The IGNORE keyword causes rows with duplicate key values (for the new UNIQUE key) to be deleted. Only the first encountered row is kept.

Renaming and Shifting Tables

Rename a table:

To change the name of clients to client_addresses:

RENAME TABLE clients TO client_addresses;

Move a table to another database (can be combined with renaming):

To move client_addresses to a database named db2:

RENAME TABLE client_addresses TO db2.client_addresses;

Re-sort data within a table (MyRocks/Aria, not typically InnoDB):

For some storage engines (excluding InnoDB where tables are ordered by the primary key), you can physically reorder rows. This does not usually apply to InnoDB unless the ORDER BY columns form the primary key.

ALTER TABLE client_addresses
ORDER BY city, name;

After this, SELECT * FROM client_addresses (without an ORDER BY clause) might return rows in this new physical order, until further data modifications occur.

Key Considerations

• Backup First: Always back up tables before making structural alterations, especially on production systems.

• Data Integrity: Be mindful of how changes (e.g., type changes, ENUM modifications, dropping columns) can affect existing data. Test changes in a development environment.

• Irreversible Actions: Operations like DROP COLUMN or DROP TABLE are generally irreversible without restoring from a backup. There's typically no confirmation prompt.

• Indexes: Understand that indexes are distinct from columns. Modifying indexed columns often requires separate steps to manage the associated indexes.

• Performance: ALTER TABLE operations on large tables can be time-consuming and resource-intensive, potentially locking the table and impacting application performance. Plan these operations during maintenance windows if possible.

CC BY-SA / Gnu FDL