SQL 9 🛢️ Modifying Tables with ALTER TABLE - KandZ

A table is created once and lives for years. The data inside it changes constantly, but the structure — the columns, the data types, the constraints — is not permanent. Requirements change. A new column is needed. A data type was chosen poorly. A constraint must be added to prevent invalid data. The ALTER TABLE statement is how the structure of an existing table is modified.

The LFCA and SQL chapters in this series covered the relational model, the RDBMS, and the CREATE TABLE statement. This chapter covers the statement that changes what CREATE TABLE defined. ALTER TABLE is a DDL statement: it modifies the schema, not the data. In most RDBMSs, it is auto-committed and cannot be rolled back within a transaction.

Key point: ALTER TABLE changes the structure of a table without destroying the data. A new column can be added. A column can be renamed. A data type can be changed. A constraint can be added or removed. Each operation modifies the table’s definition. The data that already exists is preserved, unless the operation is one that explicitly drops a column or converts data in a way that loses information. The statement is powerful and should be used with care on tables that contain production data.

Why ALTER TABLE matters

A schema is not static. It evolves with the application. New features require new columns. Bug fixes require constraints that were missing. Performance improvements require indexes and sometimes type changes. ALTER TABLE is the tool that makes the evolution possible without recreating the table and losing the data.

The new column problem. A feature is added that requires a phone_number column. The existing rows do not have a value for it. The ALTER TABLE ... ADD COLUMN statement adds the column. The existing rows get the default value, or NULL if no default is specified. The application can populate the new column as needed.

The wrong type problem. A price column was created as REAL and is now causing rounding errors. The ALTER TABLE ... ALTER COLUMN statement changes the type to NUMERIC(10, 2). The existing values are converted. In PostgreSQL, changing a column type rewrites the table, which can be slow on large tables, but the data is preserved .

The missing constraint problem. A users table does not have a UNIQUE constraint on the email column. Duplicate emails have been inserted. The ALTER TABLE ... ADD CONSTRAINT statement adds the constraint. If duplicates exist, the statement fails. The duplicates must be resolved first.

The naming problem. A column is named fname and the team has decided that first_name is clearer. The ALTER TABLE ... RENAME COLUMN statement renames the column. The data is unchanged. The application must be updated to use the new name.

The trade-off. ALTER TABLE can be expensive. Adding a column with a default value requires rewriting every row in some databases. Changing a column type can lock the table and take minutes on a large table. Dropping a column removes data permanently. The operations must be planned, tested on a copy of the data, and executed during a maintenance window if the table is large and the application is live.

a. Adding and Dropping Columns

The ADD COLUMN clause adds a new column to an existing table. The column definition follows the same syntax as in CREATE TABLE.

ALTER TABLE employees
ADD COLUMN email VARCHAR(150) UNIQUE;

The new column is added to every existing row. If the column has a DEFAULT clause, the default value is used for existing rows. If the column is NOT NULL and has no default, the statement fails when the table is not empty, because the existing rows have no value for the new column .

-- Add a column with a default
ALTER TABLE employees
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- Add a nullable column
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50);

The DROP COLUMN clause removes a column from the table. The data in the column is lost. The clause is irreversible in most databases.

ALTER TABLE employees
DROP COLUMN middle_name;

The CASCADE clause drops the column and any objects that depend on it, such as views or foreign keys. The RESTRICT clause (the default in PostgreSQL) refuses to drop the column if any object depends on it .

-- Drop a column and any dependent objects
ALTER TABLE employees
DROP COLUMN email CASCADE;

-- Refuse to drop if dependent objects exist
ALTER TABLE employees
DROP COLUMN email RESTRICT;

b. Renaming and Altering Columns

The RENAME COLUMN clause changes the name of a column. The data and the constraints are unchanged.

ALTER TABLE employees
RENAME COLUMN fname TO first_name;

The ALTER COLUMN clause changes the data type, the default value, or the nullability of a column. The syntax varies by database.

PostgreSQL uses ALTER COLUMN with TYPE, SET DEFAULT, DROP DEFAULT, SET NOT NULL, and DROP NOT NULL:

-- Change the data type
ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(10, 2);

-- Set a default
ALTER TABLE employees
ALTER COLUMN is_active SET DEFAULT TRUE;

-- Drop a default
ALTER TABLE employees
ALTER COLUMN is_active DROP DEFAULT;

-- Add NOT NULL
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL
ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

Changing a column type in PostgreSQL rewrites the table. On a large table, this can take minutes and requires an ACCESS EXCLUSIVE lock, which blocks all reads and writes .

MySQL uses MODIFY COLUMN to change the data type and CHANGE COLUMN to change both the name and the type:

-- Change the data type
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);

-- Change the name and type
ALTER TABLE employees
CHANGE COLUMN fname first_name VARCHAR(50);

SQL Server uses ALTER COLUMN to change the data type:

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(10, 2);

c. Adding and Dropping Constraints

Constraints are added with ADD CONSTRAINT and dropped with DROP CONSTRAINT. The syntax for adding a constraint is the same as in CREATE TABLE.

-- Add a primary key
ALTER TABLE employees
ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id);

-- Add a foreign key
ALTER TABLE loans
ADD CONSTRAINT fk_loan_member
FOREIGN KEY (member_id) REFERENCES members(member_id)
ON DELETE CASCADE;

-- Add a unique constraint
ALTER TABLE members
ADD CONSTRAINT unique_email UNIQUE (email);

-- Add a check constraint
ALTER TABLE employees
ADD CONSTRAINT salary_positive CHECK (salary >= 0);

The ADD CONSTRAINT statement validates the existing data. If any row violates the constraint, the statement fails. The constraint is not added. The data must be corrected first.

Dropping a constraint requires the constraint name. The name is either the one specified when the constraint was created or the one the database generated.

-- Drop a named constraint
ALTER TABLE employees
DROP CONSTRAINT salary_positive;

-- Drop a constraint in MySQL (syntax varies)
ALTER TABLE employees
DROP CHECK salary_positive;

-- Drop a primary key in MySQL
ALTER TABLE employees
DROP PRIMARY KEY;

In PostgreSQL, the DROP CONSTRAINT clause accepts CASCADE and RESTRICT. The CASCADE option drops the constraint and any objects that depend on it. The RESTRICT option refuses to drop if dependent objects exist.

Complete Example Session

This session modifies the library schema from the previous chapter.

-- ============================================
-- PART 1: THE EXISTING SCHEMA
-- ============================================

-- Books: ISBN (PK), Title, Author, Genre, Quantity
-- Members: MemberID (PK), Name, Email (UNIQUE), Phone
-- Loans: LoanID (PK), MemberID (FK), ISBN (FK), LoanDate, ReturnDate

-- ============================================
-- PART 2: ADD A COLUMN
-- ============================================

ALTER TABLE Members
ADD COLUMN JoinDate DATE DEFAULT CURRENT_DATE;

-- The JoinDate column is added to every member.
-- Existing rows get the current date as the default.

-- ============================================
-- PART 3: ADD A COLUMN WITH NOT NULL
-- ============================================

ALTER TABLE Members
ADD COLUMN IsActive BOOLEAN NOT NULL DEFAULT TRUE;

-- The IsActive column is added.
-- The default TRUE satisfies the NOT NULL constraint for existing rows.

-- ============================================
-- PART 4: RENAME A COLUMN
-- ============================================

ALTER TABLE Members
RENAME COLUMN Phone TO PhoneNumber;

-- The column is renamed.
-- The data and constraints are unchanged.

-- ============================================
-- PART 5: CHANGE A DATA TYPE
-- ============================================

ALTER TABLE Books
ALTER COLUMN Quantity TYPE BIGINT;

-- The Quantity column is changed from INTEGER to BIGINT.
-- The existing values are converted.

-- ============================================
-- PART 6: DROP A COLUMN
-- ============================================

ALTER TABLE Members
DROP COLUMN PhoneNumber;

-- The column and its data are removed.
-- The operation is irreversible.

-- ============================================
-- PART 7: ADD A CHECK CONSTRAINT
-- ============================================

ALTER TABLE Books
ADD CONSTRAINT quantity_non_negative CHECK (Quantity >= 0);

-- The constraint is added.
-- If any row has a negative Quantity, the statement fails.

-- ============================================
-- PART 8: ADD A FOREIGN KEY
-- ============================================

ALTER TABLE Loans
ADD CONSTRAINT fk_loan_member
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
ON DELETE CASCADE ON UPDATE CASCADE;

-- The foreign key is added.
-- If any loan references a non-existent member, the statement fails.

-- ============================================
-- PART 9: DROP A CONSTRAINT
-- ============================================

ALTER TABLE Books
DROP CONSTRAINT quantity_non_negative;

-- The constraint is removed.
-- The data is unchanged.

-- ============================================
-- PART 10: THE SCHEMA SUMMARY
-- ============================================

-- Books: ISBN (PK), Title, Author, Genre, Quantity (BIGINT)
-- Members: MemberID (PK), Name, Email (UNIQUE), JoinDate, IsActive
-- Loans: LoanID (PK), MemberID (FK), ISBN (FK), LoanDate, ReturnDate

The ten parts cover the existing schema, adding a column, adding a column with NOT NULL, renaming a column, changing a data type, dropping a column, adding a check constraint, adding a foreign key, dropping a constraint, and the updated schema summary.

Quick Reference

The ALTER TABLE Operations

Operation	Purpose
`ADD COLUMN`	Add a new column
`DROP COLUMN`	Remove a column
`RENAME COLUMN`	Rename a column
`ALTER COLUMN`	Change type, default, or nullability
`ADD CONSTRAINT`	Add a constraint
`DROP CONSTRAINT`	Remove a constraint
`RENAME TO`	Rename the table

The PostgreSQL Syntax

Operation	Syntax
Add column	`ADD COLUMN name type`
Drop column	`DROP COLUMN name [CASCADE\|RESTRICT]`
Rename column	`RENAME COLUMN old TO new`
Change type	`ALTER COLUMN name TYPE newtype`
Set default	`ALTER COLUMN name SET DEFAULT value`
Drop default	`ALTER COLUMN name DROP DEFAULT`
Set NOT NULL	`ALTER COLUMN name SET NOT NULL`
Drop NOT NULL	`ALTER COLUMN name DROP NOT NULL`
Add constraint	`ADD CONSTRAINT name ...`
Drop constraint	`DROP CONSTRAINT name [CASCADE\|RESTRICT]`

The MySQL Syntax

Operation	Syntax
Add column	`ADD COLUMN name type`
Drop column	`DROP COLUMN name`
Change column	`CHANGE COLUMN old new type`
Modify column	`MODIFY COLUMN name type`
Add constraint	`ADD CONSTRAINT name ...`
Drop primary key	`DROP PRIMARY KEY`
Drop foreign key	`DROP FOREIGN KEY name`
Drop index	`DROP INDEX name`

The SQL Server Syntax

Operation	Syntax
Add column	`ADD name type`
Drop column	`DROP COLUMN name`
Alter column	`ALTER COLUMN name type`
Add constraint	`ADD CONSTRAINT name ...`
Drop constraint	`DROP CONSTRAINT name`

The Table Operations

Operation	Syntax
Rename table	`ALTER TABLE old RENAME TO new`
Change owner	`ALTER TABLE name OWNER TO user`
Set schema	`ALTER TABLE name SET SCHEMA schema`

Best Practices

✅ Do This:

-- Add a column with a default for existing rows
ALTER TABLE employees ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE; -- ✅

-- Name constraints for easier management
ALTER TABLE employees ADD CONSTRAINT salary_positive CHECK (salary >= 0); -- ✅

-- Test ALTER TABLE on a copy of the data first
-- The operation may lock the table.                              -- ✅

-- Use CASCADE carefully when dropping columns or constraints
ALTER TABLE employees DROP COLUMN email CASCADE;                  -- ✅

❌ Don’t Do This:

-- Don't add a NOT NULL column without a default
ALTER TABLE employees ADD COLUMN email VARCHAR(150) NOT NULL;     -- ❌ fails if data exists

-- Don't drop a column without checking dependencies
ALTER TABLE employees DROP COLUMN email;  -- may break views     -- ❌

-- Don't change a column type on a live table without planning
ALTER TABLE orders ALTER COLUMN total TYPE NUMERIC(10, 2);        -- ❌ may lock the table

-- Don't forget that DDL is auto-committed in most databases
-- ALTER TABLE cannot be rolled back within a transaction.       -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
ADD COLUMN NOT NULL fails	Existing rows have no value	Add a default or the column nullable
ADD CONSTRAINT fails	Existing rows violate the constraint	Fix the data first
DROP COLUMN fails	Dependent objects exist	Use `CASCADE` or drop dependents
Change type fails	Values cannot be converted	Fix the data first
Slow ALTER TABLE	Table is large	Plan a maintenance window

Real-World Examples

1. Add a Column

ALTER TABLE employees ADD COLUMN email VARCHAR(150);

2. Add a Column with Default

ALTER TABLE employees ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

3. Drop a Column

ALTER TABLE employees DROP COLUMN email;

4. Rename a Column

ALTER TABLE employees RENAME COLUMN fname TO first_name;

5. Change a Type

ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(10, 2);

6. Set NOT NULL

ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

7. Add a Constraint

ALTER TABLE employees ADD CONSTRAINT salary_positive CHECK (salary >= 0);

8. Drop a Constraint

ALTER TABLE employees DROP CONSTRAINT salary_positive;

9. Rename a Table

ALTER TABLE employees RENAME TO staff;

10. Add a Foreign Key

ALTER TABLE loans ADD CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES members(member_id);

Visual

The ALTER TABLE Operations

┌──────────────────────────────────────────────┐
│  ALTER TABLE OPERATIONS                      │
│                                              │
│  ADD COLUMN:                                 │
│    └─ Add a new column                       │
│                                              │
│  DROP COLUMN:                                │
│    └─ Remove a column and its data           │
│                                              │
│  RENAME COLUMN:                              │
│    └─ Change the column name                 │
│                                              │
│  ALTER COLUMN:                               │
│    └─ Change type, default, nullability      │
│                                              │
│  ADD CONSTRAINT:                             │
│    └─ Add a rule                             │
│                                              │
│  DROP CONSTRAINT:                            │
│    └─ Remove a rule                          │
│                                              │
└──────────────────────────────────────────────┘

The ADD COLUMN Decision

┌──────────────────────────────────────────────┐
│  ADD COLUMN DECISION                         │
│                                              │
│  Does the column need a value for existing   │
│  rows?                                       │
│    │                                         │
│    ├─ YES → Add with DEFAULT                 │
│    │                                         │
│    └─ NO  → Add nullable                     │
│                                              │
│  Is the column NOT NULL?                     │
│    │                                         │
│    ├─ YES → Must have a DEFAULT              │
│    │                                         │
│    └─ NO  → Nullable is fine                 │
│                                              │
└──────────────────────────────────────────────┘

The Constraint Validation

┌──────────────────────────────────────────────┐
│  ADD CONSTRAINT VALIDATION                   │
│                                              │
│  ALTER TABLE ADD CONSTRAINT:                 │
│    1. Database scans existing rows           │
│    2. For each row, checks the constraint    │
│    3. If any row violates:                   │
│         └─ Statement fails                   │
│         └─ Constraint not added              │
│    4. If all rows pass:                      │
│         └─ Constraint added                  │
│                                              │
│  Fix the data before adding the constraint.  │
│                                              │
└──────────────────────────────────────────────┘

The DDL Auto-Commit

┌──────────────────────────────────────────────┐
│  DDL AUTO-COMMIT                             │
│                                              │
│  BEGIN;                                      │
│    ALTER TABLE employees ADD COLUMN email;   │
│    -- auto-committed here                    │
│  ROLLBACK;                                   │
│                                              │
│  The column still exists.                    │
│  DDL cannot be rolled back in most RDBMSs.   │
│                                              │
│  Plan ALTER TABLE operations carefully.      │
│                                              │
└──────────────────────────────────────────────┘

Summary

Item	Value
Add column	`ALTER TABLE t ADD COLUMN c type`
Drop column	`ALTER TABLE t DROP COLUMN c`
Rename column	`ALTER TABLE t RENAME COLUMN old TO new`
Change type	`ALTER TABLE t ALTER COLUMN c TYPE type`
Set default	`ALTER TABLE t ALTER COLUMN c SET DEFAULT v`
Set NOT NULL	`ALTER TABLE t ALTER COLUMN c SET NOT NULL`
Add constraint	`ALTER TABLE t ADD CONSTRAINT name ...`
Drop constraint	`ALTER TABLE t DROP CONSTRAINT name`
Rename table	`ALTER TABLE old RENAME TO new`
DDL rollback	Not supported in most RDBMSs

Key takeaways:

ALTER TABLE modifies the structure of an existing table. It adds columns, drops columns, renames columns, changes data types, and adds or removes constraints. The data that already exists is preserved, unless the operation explicitly removes it .
Adding a NOT NULL column requires a default value. If the table is not empty, the existing rows have no value for the new column. The DEFAULT clause supplies one. Without it, the statement fails .
Changing a column type rewrites the table in PostgreSQL. The operation requires an ACCESS EXCLUSIVE lock, which blocks all reads and writes. On a large table, this can take minutes and should be planned .
Adding a constraint validates the existing data. The database scans the table and checks whether every row satisfies the constraint. If any row violates it, the statement fails and the constraint is not added. The data must be corrected first .
Dropping a column removes the data. The operation is irreversible in most databases. The CASCADE clause drops dependent objects. The RESTRICT clause refuses to drop if dependents exist .
The syntax varies by database. PostgreSQL uses ALTER COLUMN ... TYPE and RENAME COLUMN. MySQL uses MODIFY COLUMN and CHANGE COLUMN. SQL Server uses ALTER COLUMN. The core operations are the same, but the syntax differs .
DDL is auto-committed in most RDBMSs. The ALTER TABLE statement cannot be rolled back within a transaction. The changes take effect immediately. Plan the operations carefully, especially on production tables.

Remember: ALTER TABLE is how a schema evolves. New columns are added. Data types are corrected. Constraints are added to enforce rules that were missing. Columns are renamed to reflect their meaning. The statement modifies the structure without destroying the data. But the operations can be expensive, and DDL cannot be rolled back. Test on a copy, plan a maintenance window, and execute with care.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!