A database table is rarely unchanged throughout the entire life of an application. When a system is first developed, a table may contain only the information that is known at that time. Later, a new requirement may introduce another field, require a larger text column, or require a relationship with another table.
SQL provides the ALTER TABLE statement for these structural changes. Instead of deleting an existing table and creating it again, a developer can use ALTER TABLE to change selected parts of its definition.
For example, imagine that a Student table originally stores only a student's ID and name. After an application starts collecting email addresses, the existing table can be extended with an Email column:
ALTER TABLE Student ADD Email VARCHAR(100);
The existing table remains in place, while its structure is extended to accommodate the new requirement.
ALTER TABLE is a SQL Data Definition Language (DDL) statement used to change the definition of an existing table. Depending on the database management system, it can be used to perform operations such as:
The exact syntax is not identical across all database systems. Therefore, when writing production SQL, the DBMS being used should always be considered.
The general form can be represented as:
ALTER TABLE table_name operation;
Here, table_name is the existing table and operation describes the structural change to be performed.
For example:
ALTER TABLE Student ADD Email VARCHAR(100);
This statement modifies the definition of the Student table by adding an Email column.
Adding a column is one of the most common ALTER TABLE operations. It is useful when an application needs to start storing information that was not included in the original table design.
Suppose the original table is:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);
Later, the application needs to store email addresses. The table can be changed as follows:
ALTER TABLE Student ADD Email VARCHAR(100);
The logical structure is now:
| Column | Data Type | Purpose |
|---|---|---|
| StudentID | INT | Identifies the student |
| StudentName | VARCHAR(100) | Stores the student's name |
| VARCHAR(100) | Stores the student's email address |
Notice that ALTER TABLE changes the table definition. It does not mean that an existing row automatically receives meaningful business data for the new column.
When several new attributes are required, multiple columns can sometimes be introduced in one ALTER TABLE statement. The exact syntax depends on the DBMS.
For example, in MySQL:
ALTER TABLE Employee ADD Department VARCHAR(50), ADD JoiningDate DATE;
This extends the Employee table with two new attributes.
A practical reason for doing this might be an HR application that originally stored only employee identification details but later needs department and joining-date information.
A column may need to be changed when the original definition no longer fits the application's requirements. For example, a system may initially allow names up to 50 characters and later require a larger limit.
In MySQL, the MODIFY clause can be used:
ALTER TABLE Employee MODIFY EmployeeName VARCHAR(150);
The important point is that changing a column definition should be done carefully. A change that makes a data type smaller or less compatible can cause conversion problems or data loss.
Suppose a column currently contains values longer than 50 characters:
EmployeeName VARCHAR(100)
Changing it directly to:
EmployeeName VARCHAR(30)
may not be safe because existing values may not fit into the new definition.
Therefore, column changes should be checked against the existing data before they are applied.
Different DBMS products use different syntax for modifying columns.
For example, SQL Server uses ALTER COLUMN:
ALTER TABLE Employee ALTER COLUMN EmployeeName VARCHAR(150);
This is an important distinction for students because SQL is a standard language, but individual database systems implement some DDL features differently.
Sometimes a column name is technically correct but does not follow the naming convention used by the rest of the database. A rename can improve clarity without changing the meaning of the stored data.
For example:
ALTER TABLE Student RENAME COLUMN Name TO StudentName;
After the operation, the column is called StudentName.
Renaming a column should not be treated as a purely cosmetic operation. Application code, reports, queries, stored procedures, APIs, and other database objects may depend on the old name.
When a column is no longer required, it can be removed using DROP COLUMN.
ALTER TABLE Student DROP COLUMN Email;
This removes the Email column from the table.
Dropping a column is different from simply ignoring it. The column and its stored values are removed from the table structure. If the information is needed later and no backup exists, recovering it may not be straightforward.
Before removing a column, check:
A table may initially be created without a primary key and have one added later.
ALTER TABLE Student ADD PRIMARY KEY (StudentID);
This operation will succeed only when the existing StudentID values satisfy the requirements of a primary key. Duplicate or NULL values must be dealt with before the constraint can be applied.
This is a useful example of why ALTER TABLE is closely connected with database maintenance: structural rules can be strengthened as the design develops.
If an application decides that email addresses must be unique, a UNIQUE constraint can be added to an existing table.
ALTER TABLE Student ADD CONSTRAINT UQ_Student_Email UNIQUE (Email);
Before applying the constraint, existing duplicate values should be identified and corrected. Otherwise, the database may reject the operation.
A CHECK constraint can be used to enforce a rule on values stored in a column. For example, suppose a Student table contains an Age column and the application requires a minimum age of 18:
ALTER TABLE Student ADD CONSTRAINT CK_Student_Age CHECK (Age >= 18);
The database can then reject values that violate this condition, subject to the behavior of the particular DBMS.
ALTER TABLE can also be used to introduce a relationship between existing tables.
Suppose these two tables already exist:
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT
);
The relationship can then be added:
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID);
After this relationship is established, values in Employee.DepartmentID must satisfy the referential-integrity rules defined by the foreign key.
A constraint may need to be removed when the database requirements change.
For example, if a UNIQUE constraint was previously created with the name UQ_Student_Email, it can be removed using the syntax supported by the selected DBMS.
A commonly used form is:
ALTER TABLE Student DROP CONSTRAINT UQ_Student_Email;
MySQL and other database systems can use different syntax for particular constraint types. Always check the DBMS documentation before using a production migration.
ALTER TABLE can also be used to change a table's name in database systems that support the corresponding syntax.
ALTER TABLE Student RENAME TO Students;
After renaming, queries that refer to the old table name must be updated.
For example, this query:
SELECT * FROM Student;
would need to use the new table name after the rename:
SELECT * FROM Students;
One of the most important ideas to understand is that ALTER TABLE changes a structure that may already contain data. The effect therefore depends on the operation.
| Operation | Possible Effect on Existing Data |
|---|---|
| ADD column | Existing rows receive the column according to the DBMS rules and column definition. |
| Change data type | Existing values may need conversion. |
| RENAME column | Stored values remain, but dependent queries may need changes. |
| DROP column | Values stored in that column are removed. |
| ADD constraint | Existing records may be checked for violations. |
A common beginner mistake is confusing ALTER TABLE with UPDATE. They perform completely different jobs.
| ALTER TABLE | UPDATE |
|---|---|
| Changes table structure | Changes values stored in rows |
| Used for DDL operations | Used for DML operations |
| Can add or remove columns | Changes existing column values |
| Example: ADD Email | Example: UPDATE Student SET Email = ... |
For example, adding an Email column requires ALTER TABLE:
ALTER TABLE Student ADD Email VARCHAR(100);
Putting an email address into that column is a different operation:
UPDATE Student SET Email = 'student@example.com' WHERE StudentID = 101;
Understanding this distinction is important because one statement changes the schema, while the other changes the data.
Consider a small college application. At the beginning of development, the Student table contains only basic identification information:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);
After the application is deployed, the college asks for email addresses:
ALTER TABLE Student ADD Email VARCHAR(100);
Later, the application needs admission dates:
ALTER TABLE Student ADD AdmissionDate DATE;
The college then decides that each email address should be unique:
ALTER TABLE Student ADD CONSTRAINT UQ_Student_Email UNIQUE (Email);
Finally, the development team decides that the original column name StudentName is already clear enough and leaves it unchanged. This illustrates an important database-design principle: not every possible structural change should be made simply because SQL provides the ability to make it.
Suppose an online store initially creates:
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(8,2)
);
The store later starts tracking available inventory:
ALTER TABLE Product ADD StockQuantity INT;
After some time, the business requires a longer product description:
ALTER TABLE Product ADD Description VARCHAR(500);
The original table has therefore evolved as the application gained new requirements. There was no need to recreate the entire Product table simply to introduce these additional attributes.
SQL syntax for ALTER TABLE is not completely uniform across database products. Students should therefore avoid assuming that a command written for MySQL will always work unchanged in SQL Server, PostgreSQL, or Oracle.
| Task | Example | Note |
|---|---|---|
| Add column | ALTER TABLE Student ADD Email VARCHAR(100); |
Common operation across major systems, with syntax variations. |
| MySQL modify column | ALTER TABLE Student MODIFY Name VARCHAR(150); |
MySQL syntax. |
| SQL Server modify column | ALTER TABLE Student ALTER COLUMN Name VARCHAR(150); |
SQL Server syntax. |
| Rename column | ALTER TABLE Student RENAME COLUMN Name TO StudentName; |
Supported syntax varies by DBMS/version. |
When writing SQL for a real project, identify the database engine first. This prevents migration scripts from failing because of dialect-specific syntax.
Structural changes should be treated as database migrations rather than casual edits, especially when the table contains important production data.
This workflow is particularly important for operations such as dropping columns, changing data types, or adding constraints to tables that already contain a large amount of data.
| Requirement | Typical Statement |
|---|---|
| Add a column | ALTER TABLE Student ADD Email VARCHAR(100); |
| Remove a column | ALTER TABLE Student DROP COLUMN Email; |
| Rename a column | ALTER TABLE Student RENAME COLUMN Name TO StudentName; |
| Add primary key | ALTER TABLE Student ADD PRIMARY KEY (StudentID); |
| Add foreign key | ALTER TABLE Employee ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID); |
| Rename table | ALTER TABLE Student RENAME TO Students; |
These statements are representative examples. Exact syntax for modifying columns and constraints should be checked against the database system being used.
Create the following table:
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Department VARCHAR(50)
);
Now try to perform these operations:
Working through these changes is a useful way to understand how a database schema evolves during application development.
ALTER TABLE provides a controlled way to evolve an existing database table as application requirements change. Instead of rebuilding a table for every structural adjustment, developers can add columns, change definitions, rename elements, remove obsolete fields, and introduce constraints using appropriate ALTER TABLE operations.
The most important skill is not memorizing every ALTER TABLE command. It is understanding what structural change is required, how that change affects existing data and dependent applications, and which syntax is supported by the selected database system.