A2-03-12.DDL-MySQL DROP COLUMN
转载自：http://www.mysqltutorial.org/mysql-drop-column/
MySQL DROP COLUMN
Summary: in this tutorial, we will show you how to drop a column from a table using the MySQL DROP COLUMN statement.
Introduction to MySQL DROP COLUMN statement
In some situations, you want to remove one or more columns from a table. In such cases, you use the ALTER TABLE DROP COLUMN statement as follows:
|
1
2
|
ALTER TABLE table
DROP COLUMN column;
|
Let’s examine the statement in more detail:
- First, you specify the table that contains the column you want to remove after the
ALTER TABLEclause. - Second, you put the name of the column following the
DROP COLUMNclause.
Note that the keyword COLUMN is optional so you can use the shorter statement as follows:
|
1
2
|
ALTER TABLE table
DROP column;
|
To remove multiple columns from a table at the same time, you use the following syntax:
|
1
2
3
4
|
ALTER TABLE table
DROP COLUMN column_1,
DROP COLUMN column_2,
…;
|
There are some important points you should remember before you remove a column from a table:
- Removing a column from a table makes all database objects such as stored procedures, views, triggers, etc., that depend on the column invalid. For example, you may have a stored procedure that references to a column. When you remove the column, the stored procedure will become invalid. To fix it, you have to manually change the stored procedure’s code manually.
- The code from other applications that depends on the removed column must be also changed, which takes time and efforts.
- Removing a column from a large table can impact the performance of the database.
MySQL DROP COLUMN examples
First, we create a table named posts for the demonstration.
|
1
2
3
4
5
6
7
8
|
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
excerpt VARCHAR(400),
content TEXT,
created_at DATETIME,
updated_at DATETIME
);
|
Second, to remove the excerpt column, you use the ALTER TABLE statement as follows:
|
1
2
|
ALTER TABLE posts
DROP COLUMN excerpt;
|
Third, to remove both created_at and updated_at columns at the same time, you use the following statement:
|
1
2
3
|
ALTER TABLE posts
DROP COLUMN created_at,
DROP COLUMN updated_at;
|
MySQL drop a column which is a foreign key example
If you remove the column that is a foreign key, MySQL will issue an error. Let’s demonstrate the idea.
First, create a table named categories:
|
1
2
3
4
|
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
|
Second, add the category_id column to the posts table.
|
1
|
ALTER TABLE posts ADD COLUMN category_id INT NOT NULL;
|
Third, make the category_id column as a foreign key that references to the id column of the categories table.
|
1
2
3
4
|
ALTER TABLE posts
ADD CONSTRAINT fk_cat
FOREIGN KEY (category_id)
REFERENCES categories(id) ON DELETE CASCADE;
|
Fourth, drop the category_id column from the posts table.
|
1
2
|
ALTER TABLE posts
DROP COLUMN category_id;
|
MySQL issued an error message:
|
1
|
Error Code: 1553. Cannot drop index 'fk_cat': needed in a foreign key constraint
|
To avoid this error, you must remove the foreign key constraint before dropping the column.
In this tutorial, we have shown you how to use MySQL DROP COLUMN statement to remove one or more columns from a table.
