SQL ALTER TABLE Statement

Summary: in this tutorial, you’ll learn how to use the SQL ALTER TABLE statement to change the structure of an existing table.

Introduction to SQL ALTER TABLE statement #

In SQL, the ALTER TABLE statement changes the structure of an existing table.

Here’s the syntax of the ALTER TABLE statement:

ALTER TABLE table_name
action;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, provide the name of the table you want to change in the ALTER TABLE clause.
• Second, specify an action you want to perform.

SQL allows you to perform the following actions:

• Adding a Column
• Dropping a Column
• Modifying a Column
• Renaming a Column
• Adding a Constraint
• Dropping a Constraint
• Renaming the Table
• Adding a Primary Key
• Dropping a Primary Key
• Adding a Foreign Key
• Dropping a Foreign Key

We’ll create a table called travel_requests for practicing with the ALTER TABLE statement:

CREATE TABLE travel_requests (
  request_id INT,
  employee_id INT,
  destination VARCHAR(255) NOT NULL,
  end_date DATE,
  purpose TEXT,
  status VARCHAR(20),
  note TEXT
);Code language: SQL (Structured Query Language) (sql)

Try it

Adding a column #

To add a new column to a table, you use the ADD COLUMN clause:

ALTER TABLE table_name
ADD colum_name datatype constraint;Code language: SQL (Structured Query Language) (sql)

For example, the following statement adds the start_date column to the travel_requests table:

ALTER TABLE travel_requests
ADD COLUMN start_date DATE NOT NULL;Code language: SQL (Structured Query Language) (sql)

Try it

Dropping a column #

To remove a column from a table, you use the DROP COLUMN clause:

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

The following query drops the note column from the travel_requests table:

ALTER TABLE travel_requests
DROP COLUMN note;Code language: SQL (Structured Query Language) (sql)

Try it

Modifying a column #

To change the attribute of an existing column, you use the ALTER COLUMN clause:

ALTER TABLE table_name
ALTER COLUMN column_name SET datatype;Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the data type of the purpose column to VARCHAR(255):

ALTER TABLE travel_requests
ALTER COLUMN purpose
SET DATA TYPE VARCHAR(255);Code language: SQL (Structured Query Language) (sql)

Try it

Renaming a column #

To rename a column, you use the RENAME COLUMN clause:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the column status to approval_status:

ALTER TABLE travel_requests
RENAME COLUMN status TO approval_status;Code language: SQL (Structured Query Language) (sql)

Try it

Adding a constraint #

To add a new constraint to a table, you use the ADD CONSTRAINT clause:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint;Code language: SQL (Structured Query Language) (sql)

For example, the following statement adds a CHECK constraint to the travel_requests table to ensure that the end date is on or after the start date:

ALTER TABLE travel_requests
ADD CONSTRAINT check_dates 
CHECK(end_date >= start_date);Code language: SQL (Structured Query Language) (sql)

Try it

Dropping a constraint #

To delete a constraint from a table, you use the DROP CONSTRAINT clause:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;Code language: SQL (Structured Query Language) (sql)

For example, the following statement drops the constraint check_dates from the travel_requests table:

ALTER TABLE travel_requests
DROP CONSTRAINT check_dates;Code language: SQL (Structured Query Language) (sql)

Try it

Adding a primary key #

If a table does not have a primary key, you can add one using the ADD PRIMARY KEY clause:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);Code language: SQL (Structured Query Language) (sql)

The following statement makes the request_id as the primary key of the requests table:

ALTER TABLE travel_requests
ADD PRIMARY KEY (request_id);Code language: SQL (Structured Query Language) (sql)

Try it

Dropping a primary key #

To drop a primary key from a table, you use the DROP PRIMARY KEY clause:

ALTER TABLE table_name
DROP PRIMARY KEY;

The following example removes the primary key from the travel_requests table:

ALTER TABLE travel_requests
DROP PRIMARY KEY;

Note that the Playground does not support this syntax. Instead, you need to specify the primary key name explicitly in the DROP CONSTRAINT clause:

ALTER TABLE travel_requests
DROP CONSTRAINT travel_requests_pkey;

Try it

Adding a foreign key #

To add a foreign key constraint to a table, you use the ADD FOREGIN KEY clause:

ALTER TABLE table1
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1) REFERENCES table2(column2);Code language: SQL (Structured Query Language) (sql)

The following statement adds a foreign key constraint that includes the employee_id column as a foreign key column and references to the employee_id column of the employees table:

ALTER TABLE travel_requests
ADD CONSTRAINT fk_employee 
FOREIGN KEY (employee_id) 
REFERENCES employees (employee_id);Code language: SQL (Structured Query Language) (sql)

Try it

Dropping a foreign key #

To delete a foreign key, you use the DROP FOREIGN KEY clause:

ALTER TABLE table_name
DROP CONSTRAINT fk_name;

For example, the following statement removes the foreign key constraint fk_employee from the travel_requests table:

ALTER TABLE travel_requests
DROP CONSTRAINT fk_employee;

Renaming a table #

To change the name of a table, you use the RENAME TO clause:

ALTER TABLE table_name
RENAME TO new_name;Code language: SQL (Structured Query Language) (sql)

For example, the following statement changes the travel_requests table to requests:

ALTER TABLE travel_requests
RENAME TO requests;Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the ALTER TABLE statement to modify the structure of an existing table.

Quiz #