Guides:SQL/How do I/Insert and Remove Columns from a Table

From CoderGuide

Jump to: navigation, search

Return to the SQL How Do I Index

Contents

A table can be populated with columns at creation time; however, this article covers how to add/remove columns after a table has been created. To find out how to create a table with columns, see Guides:SQL/How do I/Create and Delete a Table.

After you have created your table, you will want to populate it with columns so that data can be stored. While not required, it is highly recommended you add an index column to your table.

In these examples, we'll be using a table "my_table2" as created in Guides:SQL/How do I/Create and Delete a Table.

mysql> describe my_table2;
+--------------+-------------------+------+-----+---------+----------------+
| Field        | Type              | Null | Key | Default | Extra          |
+--------------+-------------------+------+-----+---------+----------------+
| auto_inc_rec | int(11)           | NO   | PRI | NULL    | auto_increment | 
| integer_type | int(11)           | YES  |     | NULL    |                | 
| non_neg_int  | int(10) unsigned  | YES  |     | NULL    |                | 
| varchar_type | varchar(15)       | YES  |     | NULL    |                | 
| enum_type    | enum('A','B')     | YES  |     | NULL    |                | 
| enum_type2   | enum('a','b','c') | YES  |     | NULL    |                | 
| enum_type3   | enum('ab','ca')   | YES  |     | NULL    |                | 
| date_field   | date              | YES  |     | NULL    |                | 
| deflts_to    | int(11)           | YES  |     | NULL    |                | 
| DefWhenNull  | char(10)          | YES  |     | text    |                | 
+--------------+-------------------+------+-----+---------+----------------+


All table modifications are done using the ALTER command. It takes the following format:

ALTER [IGNORE] TABLE table_name action_list

IGNORE keyword is used in cases where an add may produce duplicate columns. In these cases, if IGNORE was not used, the table alterations would produce an error. IGNORE tells the server to ignore such errors, and, instead, destroy the old column and add a new one as specified by the ALTER command.

Keep in mind that the database server will create a completely new table with the alterations upon each alter command. After the alterations are completed without error, then the old table will be destroyed and the new table will replace it. If you are altering a very large table, then these alter commands can take a some time to complete execution.

Inserting columns

After we execute this command:

ALTER TABLE my_table2 ADD new1 INT NOT NULL DEFAULT 0 AFTER enum_type;
ALTER TABLE my_table2 ADD first INT NOT NULL DEFAULT 0 FIRST;

We see this for our table:

mysql> describe my_table2;
+--------------+-------------------+------+-----+---------+----------------+
| Field        | Type              | Null | Key | Default | Extra          |
+--------------+-------------------+------+-----+---------+----------------+
| first        | int(11)           | NO   |     | 0       |                | 
| auto_inc_rec | int(11)           | NO   | PRI | NULL    | auto_increment | 
| integer_type | int(11)           | YES  |     | NULL    |                | 
| non_neg_int  | int(10) unsigned  | YES  |     | NULL    |                | 
| varchar_type | varchar(15)       | YES  |     | NULL    |                | 
| enum_type    | enum('A','B')     | YES  |     | NULL    |                | 
| new1         | int(11)           | NO   |     | 0       |                | 
| enum_type2   | enum('a','b','c') | YES  |     | NULL    |                | 
| enum_type3   | enum('ab','ca')   | YES  |     | NULL    |                | 
| date_field   | date              | YES  |     | NULL    |                | 
| deflts_to    | int(11)           | YES  |     | NULL    |                | 
| DefWhenNull  | char(10)          | YES  |     | text    |                | 
+--------------+-------------------+------+-----+---------+----------------+

Deleting colums

Deleting columns is a little simpler:

ALTER TABLE table_name DROP column_id;

Example:

mysql> ALTER TABLE my_table2 DROP DefWhenNull;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe my_table2;
+--------------+-------------------+------+-----+---------+----------------+
| Field        | Type              | Null | Key | Default | Extra          |
+--------------+-------------------+------+-----+---------+----------------+
| first        | int(11)           | NO   |     | 0       |                | 
| auto_inc_rec | int(11)           | NO   | PRI | NULL    | auto_increment | 
| integer_type | int(11)           | YES  |     | NULL    |                | 
| non_neg_int  | int(10) unsigned  | YES  |     | NULL    |                | 
| varchar_type | varchar(15)       | YES  |     | NULL    |                | 
| enum_type    | enum('A','B')     | YES  |     | NULL    |                | 
| new1         | int(11)           | NO   |     | 0       |                | 
| enum_type2   | enum('a','b','c') | YES  |     | NULL    |                | 
| enum_type3   | enum('ab','ca')   | YES  |     | NULL    |                | 
| date_field   | date              | YES  |     | NULL    |                | 
| deflts_to    | int(11)           | YES  |     | NULL    |                | 
+--------------+-------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
Personal tools