SQL ALTER TABLE

Learn how to modify existing database tables by adding, renaming, and removing columns using the SQL ALTER TABLE statement.

What is ALTER TABLE?

The ALTER TABLE statement is used to modify the structure of an existing table without creating a completely new table. Depending on the database system, you can use it to add, rename, or remove columns and make other structural changes.

SQLite supports several useful ALTER TABLE operations, including ADD COLUMN, RENAME COLUMN, RENAME TO, and DROP COLUMN.

Basic ALTER TABLE Syntax

The general syntax depends on the operation being performed. For example, to add a new column:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Adding a New Column

The ADD COLUMN clause adds a new column to an existing table. The following example is SQLite-compatible.

DROP TABLE IF EXISTS gadgets;

CREATE TABLE gadgets (
  gadget_id INTEGER PRIMARY KEY,
  gadget_name TEXT,
  price REAL
);

INSERT INTO gadgets (gadget_name, price) VALUES
  ('Smart Lamp', 1299.00),
  ('Fitness Band', 2499.00),
  ('Desk Fan', 1799.00);

ALTER TABLE gadgets
ADD COLUMN category TEXT;

UPDATE gadgets
SET category = 'Home'
WHERE gadget_id IN (1, 3);

UPDATE gadgets
SET category = 'Wearable'
WHERE gadget_id = 2;

SELECT *
FROM gadgets
ORDER BY gadget_id;

Adding a Column with a Default Value

A new column can have a DEFAULT value. This is useful when existing rows need a value for the newly added column.

DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
  project_id INTEGER PRIMARY KEY,
  project_name TEXT NOT NULL
);

INSERT INTO projects (project_name) VALUES
  ('Website Redesign'),
  ('Mobile App'),
  ('Data Dashboard');

ALTER TABLE projects
ADD COLUMN status TEXT DEFAULT 'Planned';

SELECT *
FROM projects
ORDER BY project_id;

Renaming a Column

SQLite supports RENAME COLUMN for changing the name of an existing column.

DROP TABLE IF EXISTS members;

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  full_name TEXT,
  city TEXT
);

INSERT INTO members (full_name, city) VALUES
  ('Riya Sharma', 'Delhi'),
  ('Arjun Mehta', 'Pune'),
  ('Nisha Kapoor', 'Jaipur');

ALTER TABLE members
RENAME COLUMN full_name TO member_name;

SELECT *
FROM members
ORDER BY member_id;

Renaming a Table

The RENAME TO clause changes the name of an existing table.

DROP TABLE IF EXISTS old_products;
DROP TABLE IF EXISTS products;

CREATE TABLE old_products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  price REAL
);

INSERT INTO old_products VALUES
  (1, 'Notebook', 120.00),
  (2, 'Backpack', 950.00),
  (3, 'Water Bottle', 450.00);

ALTER TABLE old_products
RENAME TO products;

SELECT *
FROM products
ORDER BY product_id;

Dropping a Column

SQLite supports DROP COLUMN in modern SQLite versions. It removes the specified column and its data from the table.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  department TEXT,
  temporary_code TEXT
);

INSERT INTO employees VALUES
  (1, 'Aarav', 'Engineering', 'TMP101'),
  (2, 'Meera', 'Design', 'TMP102'),
  (3, 'Kabir', 'Research', 'TMP103');

ALTER TABLE employees
DROP COLUMN temporary_code;

SELECT *
FROM employees
ORDER BY employee_id;

Adding Multiple Columns

In SQLite, ADD COLUMN is used for adding a column. To keep the example compatible with SQLite, each new column is added with a separate ALTER TABLE statement.

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  event_name TEXT
);

INSERT INTO events (event_name) VALUES
  ('Science Fair'),
  ('Tech Expo'),
  ('Design Workshop');

ALTER TABLE events
ADD COLUMN event_date TEXT;

ALTER TABLE events
ADD COLUMN location TEXT;

UPDATE events
SET event_date = '2026-09-10',
    location = 'Delhi'
WHERE event_id = 1;

UPDATE events
SET event_date = '2026-09-15',
    location = 'Mumbai'
WHERE event_id = 2;

UPDATE events
SET event_date = '2026-09-20',
    location = 'Pune'
WHERE event_id = 3;

SELECT *
FROM events
ORDER BY event_id;

ALTER TABLE with a CHECK Constraint

A column can be added with a CHECK constraint when the SQLite version and existing data satisfy the constraint.

DROP TABLE IF EXISTS ratings;

CREATE TABLE ratings (
  rating_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL
);

INSERT INTO ratings (item_name) VALUES
  ('Camera'),
  ('Headphones'),
  ('Monitor');

ALTER TABLE ratings
ADD COLUMN score INTEGER CHECK (score BETWEEN 1 AND 5);

UPDATE ratings
SET score = 5
WHERE rating_id = 1;

UPDATE ratings
SET score = 4
WHERE rating_id = 2;

UPDATE ratings
SET score = 3
WHERE rating_id = 3;

SELECT *
FROM ratings
ORDER BY rating_id;

Viewing the Modified Table Structure

After changing a table, SQLite’s PRAGMA table_info() can be used to inspect its columns.

DROP TABLE IF EXISTS devices;

CREATE TABLE devices (
  device_id INTEGER PRIMARY KEY,
  device_name TEXT,
  price REAL
);

ALTER TABLE devices
ADD COLUMN brand TEXT;

ALTER TABLE devices
RENAME COLUMN device_name TO model_name;

SELECT
  cid,
  name,
  type,
  "notnull",
  dflt_value,
  pk
FROM pragma_table_info('devices')
ORDER BY cid;

ALTER TABLE Operations

Operation	Purpose	SQLite
ADD COLUMN	Adds a new column	Supported
RENAME COLUMN	Changes a column name	Supported
RENAME TO	Renames a table	Supported
DROP COLUMN	Removes a column	Supported in modern SQLite

ALTER TABLE vs UPDATE

ALTER TABLE changes the structure of a table, while UPDATE changes the values stored in existing rows.

Command	Purpose	Example
ALTER TABLE	Changes table structure	ALTER TABLE products ADD COLUMN brand TEXT;
UPDATE	Changes existing data	UPDATE products SET price = 999;

Important Points

ALTER TABLE modifies an existing table’s structure.
ADD COLUMN adds a new column.
RENAME COLUMN changes the name of an existing column.
RENAME TO changes the name of a table.
DROP COLUMN removes a column in modern SQLite versions.
ALTER TABLE does not normally change existing row values unless the operation itself requires a structural change.

Best Practices

Back up important data before making structural changes.
Check existing data before adding constraints.
Use clear and meaningful column names.
Test structural changes before applying them to production databases.
Be especially careful when dropping columns because their data is removed.
Check foreign-key relationships and dependent objects before renaming or removing columns.

The ALTER TABLE statement allows you to modify an existing table without recreating it from scratch. With operations such as adding, renaming, and removing columns, it is an essential tool for maintaining and evolving database structures.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.