SQL Lesson 17: Altering Tables

Learn how to modify existing table structures using ALTER TABLE statements.

Loading Database...

SQL Lesson 17: Altering tables

As your data changes over time, SQL provides a way for you to update your corresponding tables and database schemas by using the ALTER TABLE statement to add, remove, or modify columns and table constraints.

Adding columns

The syntax for adding a new column is similar to the syntax when creating new rows in theCREATE TABLE statement. You need to specify the data type of the column along with any potential table constraints and default values to be applied to both existing and new rows. In some databases, you can even specify where to insert the new column using the FIRST or AFTER clauses, though this is not a standard feature.

ALTER TABLE

mytable

ADD COLUMN

column DataType OptionalTableConstraint

DEFAULT default_value;

Removing columns

Dropping columns is as easy as specifying the column to drop, however, some databases (including SQLite) don't support this feature. Instead you may have to create a new table and migrate the data over.

ALTER TABLE

mytable

DROP

column_to_be_deleted;

Renaming the table

If you need to rename the table itself, you can also do that using the RENAME TO clause of the statement.

ALTER TABLE

mytable

RENAME TO

new_table_name;

SQLite Limitations

SQLite has limited ALTER TABLE support compared to other databases. It only supports adding columns and renaming tables. You cannot drop columns, modify column types, or add constraints to existing columns in SQLite.

Other changes

Each database implementation supports different methods of altering their tables, so it's always best to consult your database docs before proceeding: MySQL, Postgres, SQLite, Microsoft SQL Server.

Exercise

The director for the movie studio came by earlier to talk to you about the Movies table. They want to add a couple more columns for additional metadata that they need to store. Help them out by adding the necessary columns to the table.

Table: movies

Table: boxoffice

Query results

Run a query to see results

Exercise 17 — Tasks

Add a column named Length_minutes with the data type INTEGER to the Movies table.

Hint: Use ALTER TABLE ADD COLUMN syntax

Add a column named Language with the data type TEXT and a default value of English to the Movies table.

Add two columns to the Boxoffice table: Domestic_sales with data type INTEGER and International_sales with data type INTEGER.

Solve all tasks to continue to the next lesson.

Progress0 of 3 tasks completed