Home » SQL DROP COLUMN

SQL DROP COLUMN

Savigo
You organize your data. Now organize your savings.
Set savings goals, track your progress, and stay on course.
Get Savigo for iPhone 

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;

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)
);

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;

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;

Try it

Summary #

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

Databases #

Quiz #

Quiz

SQL ALTER TABLE … DROP COLUMN Statement

5 questions

To understand how to use the SQL ALTER TABLE … DROP COLUMN Statement to remove one or more columns from an existing table.

Was this helpful?