SQL DROP COLUMN

Summary: in this tutorial, you will learn how to use the SQL DROP COLUMN clause to remove one or more columns from an existing table.

Introduction to SQL DROP COLUMN statement #

Sometimes, you may want to drop one or more unused column from an existing table. To do that, you use the ALTER TABLE with the following syntax:

ALTER TABLE table_name
DROP COLUMN column1;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the table of the column you want to drop.
Second, provide the column name (column1) you want to drop in the DROP COLUMN clause.

When you drop a column, the database system will remove the column from the table structure and all the data in that column.

If removing column causes a constraint violation, the database system will issue an error and rejects the removal.

SQL DROP COLUMN examples #

We’ll create a new table called people for the demonstration:

CREATE TABLE people (
  person_id INT PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  date_of_birth DATE NOT NULL,
  phone VARCHAR(25),
  email VARCHAR(255)
);Code language: SQL (Structured Query Language) (sql)

Try it

Dropping one column #

The following statement uses the ALTER TABLE ... DROP COLUMN statement to drop the email column from the persons table:

ALTER TABLE people
DROP COLUMN email;Code language: SQL (Structured Query Language) (sql)

Try it

Dropping multiple columns #

The following example uses the ALTER TABLE ... DROP COLUMN statement to remove the date_of_birth and phone columns from the people table:

ALTER TABLE people
DROP COLUMN date_of_birth,
DROP COLUMN phone;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

Use the SQL DROP COLUMN statement to remove one or more columns from a table.

Databases #

Quiz #

Was this tutorial helpful ?