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