SQL ALTER TABLE Add Column: A Complete Guide

Adding a column to an existing table in SQL is a common task that you’ll need to perform as your database schema evolves. The ALTER TABLE statement allows you to modify an existing table’s structure without losing data. This guide covers how to use ALTER TABLE to add columns with practical examples.

Table of Contents

• Understanding ALTER TABLE
• Basic Syntax for Adding a Column
• Example: Adding a Column
• Adding a Column with a Default Value
• Adding a Column with NOT NULL Constraint
• Related Operations
• Conclusion

Understanding ALTER TABLE

The ALTER TABLE command is used to add, delete, or modify columns in an existing table. It can also be used to change table constraints. This tutorial focuses specifically on adding new columns.

When adding a new column, you must specify the column name, data type, and any additional constraints or default values. The operation is atomic and won’t affect existing data.

Basic Syntax for Adding a Column

Here’s the basic syntax for adding a new column to a table:

ALTER TABLE table_name
ADD column_name column_type [constraint];

• table_name: The name of the table to which you are adding a column.
• column_name: The name of the new column.
• column_type: The data type of the new column (e.g., VARCHAR, INT, DATE, etc.).
• constraint: Optional. Any additional constraint like NOT NULL, UNIQUE, or DEFAULT values.

Example: Adding a Column

Let’s start with a simple employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50)
);

-- Current data in employees table:
INSERT INTO employees (id, name, position) VALUES (1, 'Alice', 'Engineer');
INSERT INTO employees (id, name, position) VALUES (2, 'Bob', 'Manager');

SELECT * FROM employees;

Output:

+----+-------+----------+
| id | name  | position |
+----+-------+----------+
|  1 | Alice | Engineer |
|  2 | Bob   | Manager  |
+----+-------+----------+

Adding a New Column for Salary

Let’s add a new column named salary with the data type DECIMAL(10, 2), which can store numbers with up to 10 digits, including 2 decimal places:

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

This command modifies the employees table to include the new salary column. Existing rows will have NULL values for this column until you update them.

Verifying the Table Structure

Verify the column was added using DESCRIBE or SHOW COLUMNS (depending on your database system):

DESCRIBE employees;

Output:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(100)| YES  |     | NULL    |       |
| position | varchar(50) | YES  |     | NULL    |       |
| salary   | decimal(10,2)| YES |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Adding a Column with a Default Value

You can also add a column with a default value. Here’s how to add a hire_date column that defaults to the current date:

ALTER TABLE employees
ADD hire_date DATE DEFAULT CURRENT_DATE;

New rows inserted into the employees table will automatically have hire_date set to the current date when no value is provided.

Adding a Column with NOT NULL Constraint

Adding a column with a NOT NULL constraint to an existing table requires special consideration. You cannot simply add a NOT NULL column to a table that already contains data without providing a default value, as this would violate the constraint for existing rows.

The Problem with NOT NULL on Existing Data

This command will fail if the table contains existing rows:

-- This will fail on tables with existing data
ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL;

The error occurs because existing rows would have NULL values for the new column, violating the NOT NULL constraint.

Solution 1: Add Column with Default Value

The most straightforward solution is to provide a default value:

ALTER TABLE employees
ADD phone_number VARCHAR(20) NOT NULL DEFAULT 'Unknown';

This works because:

1. The column is added with the default value for all existing rows
2. The NOT NULL constraint is satisfied immediately
3. Future inserts must provide a value or use the default

Solution 2: Multi-Step Process

For more control over the values, use a multi-step approach:

-- Step 1: Add the column as nullable
ALTER TABLE employees
ADD phone_number VARCHAR(20);

-- Step 2: Update existing rows with appropriate values
UPDATE employees 
SET phone_number = 'Unknown' 
WHERE phone_number IS NULL;

-- Step 3: Add the NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone_number SET NOT NULL;

Note: The syntax for step 3 varies by database system:

• PostgreSQL: ALTER COLUMN phone_number SET NOT NULL
• MySQL: MODIFY COLUMN phone_number VARCHAR(20) NOT NULL
• SQL Server: ALTER COLUMN phone_number VARCHAR(20) NOT NULL

Solution 3: Conditional Default Values

You can set different default values based on existing data:

-- Add column with temporary default
ALTER TABLE employees
ADD department VARCHAR(50) NOT NULL DEFAULT 'TBD';

-- Update with conditional logic based on position
UPDATE employees 
SET department = CASE 
    WHEN position = 'Engineer' THEN 'Engineering'
    WHEN position = 'Manager' THEN 'Management'
    ELSE 'General'
END;

-- Optionally remove the default if you don't want it for future inserts
ALTER TABLE employees
ALTER COLUMN department DROP DEFAULT;

Best Practices for NOT NULL Columns

1. Plan ahead: Consider NOT NULL constraints during initial table design
2. Use meaningful defaults: Choose default values that make sense for your business logic
3. Test first: Always test ALTER TABLE operations on a copy of your data
4. Document changes: Keep track of schema modifications for team communication
5. Consider data migration: Plan how to populate the new column with meaningful data

Verifying NOT NULL Constraints

After adding a NOT NULL column, verify the constraint is in place:

-- Check table structure
DESCRIBE employees;

-- Verify constraint by attempting to insert NULL (should fail)
INSERT INTO employees (id, name, position, phone_number) 
VALUES (3, 'Carol', 'Analyst', NULL);
-- Error: Column 'phone_number' cannot be null

Related Operations

Now that you know how to add columns, you might also want to learn about other ALTER TABLE operations:

• How to rename columns using ALTER TABLE
• PostgreSQL: Change column type
• How to add columns in Teradata

These operations are essential for maintaining and evolving your database schema as your application requirements change.

Conclusion

Using ALTER TABLE to add columns allows for schema modifications without data loss. This flexibility helps adapt your database to evolving application requirements. Always back up your data before making schema changes and verify compatibility with existing application logic.

For more advanced database operations and to manage your SQL databases effectively, consider using Beekeeper Studio, which provides an intuitive interface for database schema management and SQL operations.