SQL ALTER TABLE Statement
An existing database table structure can be changed using the SQL (Structured Query Language) command ALTER TABLE. Within a table, it can be used to add, alter (modify), or delete (remove) columns, constraints, and indexes.
The ALTER TABLE statement in SQL allows you to make changes to an existing table structure. You can add, modify, or drop columns, constraints, or indexes as needed. You have the ability to modify the table once it has been created.
The syntax of the ALTER TABLE statement is as follows:
ALTER TABLE table_name
[ADD COLUMN column_name datatype constraint]
[ALTER COLUMN column_name datatype]
[DROP COLUMN column_name]
[ADD CONSTRAINT constraint_name constraint_type (column_name)]
[DROP CONSTRAINT constraint_name]
Below are some common examples of the ALTER TABLE statement in use:
SQL ALTER TABLE Example
Adding a new column: You can use ALTER TABLE to add a new column to an existing table.
ALTER TABLE employees
ADD COLUMN email VARCHAR(255);
Modifying an existing column:
You can change the data type of an existing column using ALTER TABLE.
ALTER TABLE orders
ALTER COLUMN order_date DATE;
Dropping a column:
To remove a column from a table, you can use the DROP COLUMN clause.
ALTER TABLE products
DROP COLUMN obsolete;
ALTER TABLE ADD MULTIPLE COLUMNS:
Syntax to add multiple columns in SQL
ALTER TABLE table_name
ADD column1 column1_definition;
ADD column2 column2_definition;
ADD column3 column3_definition;
ADD column4 column4_definition;
….
ADD column columnn_definition;
Example to Add Multiple Columns:
ALTER TABLE Students
ADD COLUMN stdID INT,
ADD COLUMN address VARCHAR(100),
ADD COLUMN rollno INT(20);
ADD COLUMN class VARCHAR(100);
alter table add primary key
CREATE TABLE Students (
stdID int NOT NULL,
Address varchar(255) NOT NULL,
class varchar(255),
Rollno int,
CONSTRAINT MY_Student PRIMARY KEY (stdID)
);
Note: In the above example, there is only ONE PRIMARY KEY (MY_Student). However, the value of the primary key is added to only ONE COLUMNS (stdID).
ALTER TABLE Persons
ADD PRIMARY KEY (stdID);
alter table, add column with default value
In MySQL ALTER TABLE syntax
ALTER TABLE table_name
ADD column_name data_type DEFAULT default_value;
alter table mysql (ALTER TABLE ADD Column syntex)
ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE Students
ADD Address varchar(255);
ALTER TABLE MODIFY COLUMN
Below is the syntax to change the data type of a column in a table.
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
MySQL ALTER TABLE MODIFY COLUMN Example
ALTER TABLE Students
ADD DOB date;
ALTER TABLE DROP COLUMN
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE Students
DROP COLUMN Address;
In SQL Server, ALTER TABLE syntax
ALTER TABLE table_name
ADD column_name data_type
CONSTRAINT constraint_name
DEFAULT (default_value);
In Postgresql, ALTER TABLE ADD COLUMN
To add a new column to an existing (old) table, you can use the ALTER TABLE ADD COLUMN statement.
ALTER TABLE table_name
ADD COLUMN new_column_name data_type constraint;
To add multiple columns to an existing (old) table, you can use multiple ADD COLUMN clauses in the ALTER TABLE statement.
ALTER TABLE table_name
ADD COLUMN column1_name data_type constraint,
ADD COLUMN column2_name data_type constraint,
…
ADD COLUMN column_name data_type constraint;
PostgreSQL ADD COLUMN statement examples
The following CREATE TABLE statement creates a new table named students with one column ID.
To drop the existing table or rename it if you have…
DROP TABLE IF EXISTS students CASCADE;
CREATE TABLE students (
stdID SERIAL PRIMARY KEY,
student_name VARCHAR NOT NULL
);
The ALTER TABLE ADD COLUMN statement is used to add the phone number column to the students table.
ALTER TABLE students
ADD COLUMN phone VARCHAR;
Modifying the structure of existing tables is made easy using the ALTER TABLE statement. You may alter columns, constraints, and more.
Take precautions before making structural changes to your database tables and use extreme care while updating tables, particularly in production situations.