REMOVING A COLUMN FROM TABLE

{Show you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.}

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 following ALTER TABLE DROP COLUMN statement:

ALTER TABLE table_name
DROP COLUMN column_name;

In this syntax:

• First, specify the name of the table that contains the column which you want to drop after the ALTER TABLE keywords.
• Second, specify the name of the column that you want to drop in theDROP COLUMN clause.

Note that the keyword COLUMN keyword in the DROP COLUMN clause is optional so you can use the shorter statement as follows:

ALTER TABLE table_name
DROP column_name;

To remove multiple columns from a table using a single ALTER TABLE statement, you use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name_1,
DROP COLUMN column_name_2,
...;Code language: SQL (Structured Query Language) (sql)

There are some important points you should remember before removing a column from a table:

• Removing a column from a table makes all database objects such as stored procedures, views, and triggersthat referencing the dropped column invalid. For example, you may have a stored procedure that refers to a column. When you remove the column, the stored procedure becomes invalid. To fix it, you have to manually change the stored procedure’s code.
• The code from other applications that depends on the dropped column must be also changed, which takes time and efforts.
• Dropping a column from a large table can impact the performance of the database during the removal time.

The SQL command describe employees; is used to display the structure of a table. In this case, it's being used to show the structure of the employees table.

When you run this command in a MySQL database, it will return a table with the following columns:

• Field: This column shows the name of each field (or column) in the employees table.
• Type: This column shows the data type of each field. This could be integer, varchar, datetime, etc., depending on how the table was defined.
• Null: This column shows whether the field can contain NULL values. If a field can contain NULL values, this will be marked as 'YES'. If not, it will be marked as 'NO'.
• Key: This column indicates whether the field is part of any index, primary key, or unique key.
• Default: This column shows the default value for the field. If no default value has been set, this will be NULL.
• Extra: This column provides additional information about the field. For example, if the field is auto-incremented, it will be indicated here.

This command is very useful when you need to understand the structure of a table, including the names of the columns, their data types, and other attributes.

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. Consider the following example.

First, create a table named categories:

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Second, add a column named category_id to the posts table.

ALTER TABLE posts
ADD COLUMN category_id INT NOT NULL;

Third, make the category_id column as a foreign key column of that references to the id column of the categories table.

ALTER TABLE posts
ADD CONSTRAINT fk_cat
FOREIGN KEY (category_id)
REFERENCES categories(id);

checking the content of the describe and post below

DESCRIBE posts;

DESCRIBE categories;

Fourth, drop the category_id column from the posts table.

ALTER TABLE posts
DROP COLUMN category_id;Code language: 

MySQL issued an error message:

Error Code: 1553. Cannot drop index 'fk_cat': needed in a foreign key constraintCode language: JavaScript (javascript)

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.