SQL ALTER TABLE Command with Examples

By Ravindra Bagale, trainer · LinkedIn · Last updated: 18 October 2026

1. Practice data

Why: every command below shows its result. If you load the same five rows, you get the same answers and can check yourself.

How:

1. Log in with mysql -u root -p.

2. Start with a fresh instagram database. If you still have one from the first SQL chapter, this removes it:

   DROP DATABASE IF EXISTS instagram;
   CREATE DATABASE instagram;
   USE instagram;
   

3. Create the users table without any rules yet:

   CREATE TABLE users (id INT, name VARCHAR(50), city VARCHAR(50), age INT);
   

4. Load five rows. Row 3 is typed twice on purpose:

   INSERT INTO users VALUES
   (1, 'amit', 'pune', 25), (2, 'neha', 'mumbai', 30), (3, 'rahul', 'nashik', 28),
   (3, 'rahul', 'nashik', 28), (4, 'sneha', NULL, NULL);
   

5. SELECT * FROM users; shows the five rows.

Notice two things we will use later: two rows are exactly the same (3, rahul, nashik, 28), and sneha has no city and no age.

The screenshots in this chapter were taken on a table with more rows, so their row counts differ from yours. The commands and the messages are the same.

2. UPDATE versus ALTER TABLE

Why: both words sound like "change", and interviewers like to ask the difference.

What:

1. UPDATE changes the data: the values inside rows. The columns stay the same.
2. ALTER TABLE changes the structure: which columns exist, their types, their names and their rules. The rows stay.

How: just read these two examples; we run real ones from section 3 on.

1. UPDATE users SET age = 26 WHERE id = 1; would change one value in one row.
2. ALTER TABLE users ADD COLUMN phone VARCHAR(15); would add a column to every row at once.
3. A quick test: if the question is about values, use UPDATE; if it is about columns, use ALTER TABLE.

3. Safe update mode

Why: UPDATE or DELETE without WHERE changes or deletes every row. One forgotten line can wipe a whole column. Safe update mode is a seat belt against that mistake.

What: with safe update mode on, MariaDB refuses an UPDATE or DELETE unless its WHERE uses a key column (such as a primary key), or the statement has a LIMIT. The setting is called SQL_SAFE_UPDATES and it belongs to your session: a new login starts again with the server default.

How:

1. See the current value: SELECT @@SQL_SAFE_UPDATES; On a fresh login it shows 0, which means off.
2. Turn it on for this session: SET SQL_SAFE_UPDATES = 1;

3. Try an update with no WHERE:

   UPDATE users SET city = 'pune';
   

4. MariaDB refuses: ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

5. A WHERE on an ordinary column is refused too, because city is not a key column. DELETE FROM users WHERE city = 'nashik'; gives the same ERROR 1175.
6. A LIMIT is accepted, because it caps the damage: UPDATE users SET city = 'delhi' WHERE name = 'sneha' LIMIT 1; answers Query OK, 1 row affected.
7. Once id is a primary key (section 12), WHERE id = 2 is accepted as well.
8. Turn it off again: SET SQL_SAFE_UPDATES = 0; The rest of this chapter runs with it off.
9. SELECT, INSERT and ALTER TABLE are never blocked by this setting.

4. ADD COLUMN: a new column for every row

Why: the app now needs each user's phone number. You don't create a new table; you add a column to the one you have.

What: ALTER TABLE ... ADD COLUMN adds a column at the end. Every existing row gets NULL in it, because nobody has given a value yet. The word COLUMN is optional: ADD phone INT works too.

How:

1. Add the column:

   ALTER TABLE users ADD COLUMN phone INT;
   

2. MariaDB answers Query OK, 0 rows affected. No row was added or removed.

3. DESC users; now lists phone with type int(11).
4. SELECT * FROM users; shows NULL in phone for all five rows.
5. Fill a value with UPDATE, as usual: UPDATE users SET phone = ... WHERE id = 1;

5. MODIFY COLUMN: change the type or the size

Why: an INT phone column is a mistake, as you find out the moment you store a real number.

What: MODIFY COLUMN gives a column a new type, size or set of rules. You write the whole column definition again. MariaDB converts every existing value; the change works only if every value fits the new type.

How:

1. Try a real mobile number in the INT column:

   UPDATE users SET phone = 9876543210 WHERE id = 1;
   

2. It fails: ERROR 1264 (22003): Out of range value for column 'phone' at row 1. The largest INT is 2,147,483,647, and a 10-digit mobile number like 9876543210 is bigger.

3. The 11 in int(11) is only a display width. It does not mean "11 digits", and it does not change the limit.
4. A number column has a second problem: it keeps only the value. A + in front and a leading 0 (as in 020... landline numbers) are lost.

5. So a phone number is text. Change the type:

   ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);
   

6. DESC users; now shows phone varchar(15).

7. Now the full number fits, plus sign and all:

   UPDATE users SET phone = '+919876543210' WHERE id = 1;
   UPDATE users SET phone = '02025501234' WHERE id = 2;
   

8. Making a column bigger is always safe: ALTER TABLE users MODIFY COLUMN phone VARCHAR(20); works.

9. Making it smaller fails if a value does not fit: MODIFY COLUMN phone VARCHAR(10) gives ERROR 1265 (01000): Data truncated for column 'phone' at row 1, because +919876543210 has 13 characters.
10. Going back from VARCHAR to INT fails too: MODIFY COLUMN phone INT gives ERROR 1264 ... Out of range value for column 'phone', because 919876543210 is bigger than the INT limit.
11. The rule to remember: small to large is safe; large to small, or to a stricter type, works only when every existing value fits.

6. NOT NULL: make a value compulsory

Why: a user without a name makes no sense in our app. You want the database itself to refuse such a row, whatever the app sends.

What: NOT NULL means the column must always have a value. An INSERT that leaves it out, or sends NULL, is rejected.

How:

1. Make name compulsory:

   ALTER TABLE users MODIFY COLUMN name VARCHAR(20) NOT NULL;
   

2. DESC users; shows NO in the Null column for name.

3. Insert a user without a name:

   INSERT INTO users (id, city) VALUES (5, 'goa');
   

4. It fails: ERROR 1364 (HY000): Field 'name' doesn't have a default value.

5. Sending NULL on purpose fails too: INSERT INTO users (id, name, city) VALUES (5, NULL, 'goa'); gives ERROR 1048 (23000): Column 'name' cannot be null.
6. A column that already holds NULL values cannot become NOT NULL. MODIFY COLUMN phone VARCHAR(20) NOT NULL gives ERROR 1265 (01000): Data truncated for column 'phone' at row 3, because three rows have no phone. MariaDB's default strict mode refuses rather than silently filling in something.
7. Fix those rows first with UPDATE, then add NOT NULL.

7. DEFAULT: a value when none is given

Why: sometimes a missing value has a sensible answer. A user who signs up without a name can be shown as "unknown", like an anonymous account.

What: DEFAULT is the value MariaDB stores when an INSERT does not mention the column. It works with or without NOT NULL.

How:

1. Keep NOT NULL and add a default:

   ALTER TABLE users MODIFY COLUMN name VARCHAR(20) NOT NULL DEFAULT 'unknown';
   

2. Run the insert that failed before:

   INSERT INTO users (id, city) VALUES (5, 'goa');
   

3. Now it works, and row 5 has the name unknown.

4. Give age a default of 18, without NOT NULL:

   ALTER TABLE users MODIFY COLUMN age INT DEFAULT 18;
   

1. Insert a user without an age:

   INSERT INTO users (id, name, city) VALUES (6, 'kiran', 'solapur');
   

2. SELECT * FROM users; shows 6 | kiran | solapur | 18.

3. A default fills only new rows that skip the column. Old rows keep their NULL: sneha's age is still NULL.
4. DESC users; shows unknown and 18 in the Default column.

8. Remove a rule: write the column again without it

Why: rules change. Maybe name should be optional again.

What: there is no "remove NOT NULL" command. MODIFY COLUMN replaces the whole column definition, so whatever you leave out is removed.

How:

1. Remove NOT NULL but keep the default:

   ALTER TABLE users MODIFY COLUMN name VARCHAR(20) DEFAULT 'unknown';
   

2. DESC users; shows Null = YES and Default = unknown.

1. Remove the default too:

   ALTER TABLE users MODIFY COLUMN name VARCHAR(20);
   

2. DESC users; shows Null = YES and Default = NULL.

3. The other side of the same rule: when you change only the size, write the rules again too, or you lose them by accident. MODIFY COLUMN name VARCHAR(30) on a NOT NULL DEFAULT 'unknown' column removes both rules.

9. DROP COLUMN, RENAME COLUMN and RENAME TO

Why: columns that nobody uses should go, and names should say what a column holds.

What:

1. DROP COLUMN removes a column and all its data.
2. RENAME COLUMN old TO new keeps the data and changes only the name.
3. RENAME TO renames the whole table.

How:

1. Remove the phone column:

   ALTER TABLE users DROP COLUMN phone;
   

2. Rename city, because it will hold full addresses:

   ALTER TABLE users RENAME COLUMN city TO address;
   

3. DESC users; now lists id, name, address, age.

4. Rename the table:

   ALTER TABLE users RENAME TO people;
   

5. SHOW TABLES; lists people. Rename it back with ALTER TABLE people RENAME TO users; (RENAME TABLE people TO users; does the same).

6. RENAME COLUMN needs MariaDB 10.5 or newer. On an older server, use CHANGE, which takes the old name, the new name and the full type: ALTER TABLE users CHANGE city address VARCHAR(50);

10. Why tables need keys

Why: look at the two identical rahul rows. Say one of them should be renamed.

How:

1. Try it by name:

   UPDATE users SET name = 'rahul k' WHERE name = 'rahul';
   

2. MariaDB answers Rows matched: 2 Changed: 2. Both rows changed.

3. WHERE id = 3 would also match both, because both have id 3.
4. No query can pick exactly one of two identical rows. The table needs a column whose value is different in every row, and never empty.

What: a key is a rule the database enforces on a column. This chapter covers two: the primary key and the unique key. The third, the foreign key, links two tables and comes in the next SQL chapter.

11. Primary key versus unique key

Why: this is the interviewer's favourite SQL question, and the answer decides how you design every table.

What:

1. Both refuse duplicates.
2. A primary key never allows NULL. Making a column the primary key makes it NOT NULL.
3. A unique key allows NULL, even in many rows, because NULL means "unknown" and is not equal to anything.
4. A table has only one primary key. Declaring two gives ERROR 1068 (42000): Multiple primary key defined. The one primary key may span several columns, for example PRIMARY KEY (a, b).
5. A table can have many unique keys: email, phone, username, each one unique.
6. DESC shows PRI for the primary key and UNI for a unique key.
7. Almost any column type can be a primary key: INT, VARCHAR(10), even DATE. A short number is the usual choice, because it is small, fast and never needs to change.

How: make id the primary key of users.

1. Try it straight away:

   ALTER TABLE users MODIFY COLUMN id INT PRIMARY KEY;
   

2. It fails: ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'. The two rahul k rows still share id 3. (An id that is NULL would block it too.)

3. Clean the data first. Remove one of the two identical rows:

   DELETE FROM users WHERE id = 3 LIMIT 1;
   

4. Now ids 1 to 6 are all different, so the ALTER TABLE from step 1 would work. Don't run it yet: in the next section we make id the primary key and add AUTO_INCREMENT in one command.

5. Best practice: give every table a primary key. SQL does not force you; a table without one is allowed. But then two identical rows can never be told apart, as you saw above.

12. AUTO_INCREMENT: let MariaDB pick the id

Why: when people type ids by hand (1, 5, 500 ...), nobody knows which id the next user should get, and two people may pick the same one.

What: AUTO_INCREMENT gives each new row the next number automatically: the highest id so far, plus 1. The column must have a key on it, normally the primary key.

How:

1. Without a key, MariaDB refuses: ALTER TABLE users MODIFY COLUMN id INT AUTO_INCREMENT; gives ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.

2. Do both in one command:

   ALTER TABLE users MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
   

3. DESC users; shows id int(11), Null NO, Key PRI, Extra auto_increment.

4. Insert without an id:

   INSERT INTO users (name, address) VALUES ('pooja', 'satara');
   

5. pooja gets id 7: the highest id was 6.

1. You may still give an id yourself, if it is free. A taken id is refused:

   INSERT INTO users (id, name, address) VALUES (1, 'meera', 'kolhapur');
   

2. That gives ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'.

3. A free id works: INSERT INTO users (id, name, address) VALUES (10, 'meera', 'kolhapur');
4. The counter jumps past it. The next insert without an id, ('arjun', 'pune'), gets 11, not 8. Ids 8 and 9 are simply never used.

13. Why AUTO_INCREMENT leaves gaps

Why: interviewers ask this too: "I deleted the last row; why didn't the next row get its id back?"

What: the table keeps a counter that only moves forward. An id handed out once is never handed out again, even if its row is deleted or its insert fails. This is by design: many sessions can insert at the same moment, and each gets its own number without waiting for the others. Going back to reuse numbers would make them clash.

How:

1. Delete arjun: DELETE FROM users WHERE id = 11;
2. Insert arjun again: INSERT INTO users (name, address) VALUES ('arjun', 'pune');
3. He gets 12, not 11.
4. A failed insert also uses up a number. In section 14 an insert of a duplicate name fails; the next successful insert skips one number.
5. So never assume ids have no gaps, and never use them to count rows. Use SELECT COUNT(*) FROM users; for that.
6. INT stops at 2,147,483,647. For a table that may grow beyond that, use BIGINT for the id.

14. Add UNIQUE to an existing column

Why: in a real app, an email or a username must belong to one person only. Here we practise on name.

How:

1. Add the rule:

   ALTER TABLE users MODIFY COLUMN name VARCHAR(20) UNIQUE;
   

2. It works because no two rows share a name. If two did, it would fail with ERROR 1062 until you fixed them.

3. DESC users; shows UNI for name, next to PRI and auto_increment for id.

4. Insert a second amit:

   INSERT INTO users (name, address) VALUES ('amit', 'mumbai');
   

5. It fails: ERROR 1062 (23000): Duplicate entry 'amit' for key 'name'. A unique key is named after its column.

6. Insert someone new: INSERT INTO users (name, address) VALUES ('isha', 'nagpur'); isha gets id 14: the failed amit insert used up 13.
7. NULL is allowed, even twice: INSERT INTO users (name, address) VALUES (NULL, 'pune'), (NULL, 'nashik'); works.
8. Remove them again with DELETE FROM users WHERE name IS NULL;
9. For a brand-new column, write the rule when you add it: ALTER TABLE users ADD COLUMN email VARCHAR(100) UNIQUE;

15. DELETE versus TRUNCATE versus DROP

Why: all three "remove" something, and the difference is a classic interview question. Picking the wrong one can cost you a table.

What:

1. DELETE removes the rows you choose with WHERE, one by one; without WHERE it removes every row. The table stays, and the AUTO_INCREMENT counter keeps counting. Safe update mode applies to it.
2. TRUNCATE TABLE empties the whole table in one go. It has no WHERE. The structure stays, and the counter starts again at 1. It is fast even on a huge table.
3. DROP TABLE removes the table itself: structure and data. DROP DATABASE removes a database with every table inside it.

How:

1. Delete the rows you choose: DELETE FROM users WHERE id = 4;
2. Delete every row: DELETE FROM users; answers Query OK, 9 rows affected.
3. Insert a row: INSERT INTO users (name) VALUES ('test'); It gets id 17: DELETE did not reset the counter.
4. Empty the table with TRUNCATE:

   TRUNCATE TABLE users;
   

1. Insert the same row again. Now it gets id 1: TRUNCATE reset the counter.
2. DESC users; still shows all four columns with their keys. Only the data went.
3. Remove the table: DROP TABLE users;
4. DESC users; now gives ERROR 1146 (42S02): Table 'instagram.users' doesn't exist.
5. Remove the whole practice database: DROP DATABASE instagram; The prompt changes to MariaDB [(none)]>.
6. SHOW DATABASES; no longer lists instagram.
7. None of these can be undone in the client. Before a DELETE, run the same WHERE with SELECT; before TRUNCATE or DROP on real data, keep a backup.

When you're done practising, type exit; and stop the EC2 instance.

16. The whole class on one page

1. UPDATE changes data; ALTER TABLE changes structure.
2. SELECT @@SQL_SAFE_UPDATES; SET SQL_SAFE_UPDATES = 1; blocks UPDATE / DELETE without a key column in WHERE or a LIMIT (ERROR 1175), for this session.
3. ALTER TABLE t ADD COLUMN c type; new column, NULL in old rows.
4. ALTER TABLE t MODIFY COLUMN c newtype; small to large is safe; large to small works only if every value fits (ERROR 1265, ERROR 1264).
5. Phone numbers are VARCHAR; INT stops at 2,147,483,647, and int(11) is only a display width.
6. NOT NULL makes a value compulsory (ERROR 1364); fix existing NULLs first.
7. DEFAULT x fills a skipped column in new rows.
8. To remove a rule, MODIFY the column without it.
9. DROP COLUMN c, RENAME COLUMN a TO b, RENAME TO newname.
10. Primary key: no duplicates, no NULL, one per table. Unique: no duplicates, NULL allowed, many per table.
11. INT PRIMARY KEY AUTO_INCREMENT: highest + 1; a taken id gives ERROR 1062; gaps after deletes and failed inserts are normal.
12. DELETE chosen rows (counter continues); TRUNCATE all rows (counter restarts); DROP the table or database.

17. Hands-on lab: change a table's structure safely

Why this lab. Apps change, so tables change. This lab adds and changes columns, adds rules and keys, and shows each error you will meet, on a small table you can throw away.

1. Make a small table
   • Command: CREATE TABLE staff (id INT, name VARCHAR(20), city VARCHAR(20)); INSERT INTO staff VALUES (1,'ravi','pune'),(2,'neha','mumbai'),(3,'amit','nashik');
   • You should see: Query OK, 0 rows affected, then Query OK, 3 rows affected.
   • Check: SELECT * FROM staff; → 3 rows.
   • Why: A separate table keeps your real practice data safe while you experiment with structure.
2. Add a column
   • Command: ALTER TABLE staff ADD COLUMN phone VARCHAR(15);
   • You should see: Query OK, 0 rows affected and Records: 0 Duplicates: 0 Warnings: 0.
   • Check: SELECT * FROM staff; shows phone as NULL for all 3 rows.
   • Why: ALTER TABLE changes structure, not data, so no row is "affected". VARCHAR, not INT, because phone numbers can start with 0 or a +.
3. Make name compulsory
   • Command: ALTER TABLE staff MODIFY COLUMN name VARCHAR(30) NOT NULL;
   • You should see: Query OK and Duplicates: 0 Warnings: 0.
   • Check: INSERT INTO staff (id, city) VALUES (4, 'goa'); → ERROR 1364 (HY000): Field 'name' doesn't have a default value
   • Why: The database itself now refuses a nameless row, whatever the app forgets to check. MODIFY rewrites the whole column, so the type is written again.
4. Add a primary key
   • Command: ALTER TABLE staff ADD PRIMARY KEY (id);
   • You should see: Query OK and Duplicates: 0 Warnings: 0.
   • Check: INSERT INTO staff (id, name) VALUES (1, 'raj'); → ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
   • Why: A primary key makes every row findable by one unique, non-NULL value. Without it, two identical rows can't be told apart.
5. Let MariaDB pick ids
   • Command: ALTER TABLE staff MODIFY COLUMN id INT AUTO_INCREMENT; INSERT INTO staff (name, city) VALUES ('raj', 'goa');
   • You should see: Query OK twice.
   • Check: SELECT id, name FROM staff WHERE name = 'raj'; → 4 | raj
   • Why: Nobody has to remember the last id; MariaDB uses the next number. The primary key stays, because MODIFY on a key column keeps the key.
6. Turn on safe updates
   • Command: SET SQL_SAFE_UPDATES = 1; UPDATE staff SET city = 'pune';
   • You should see: ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
   • Check: No row changed: SELECT city FROM staff; still shows four different cities.
   • Why: Safe mode is a seat belt: it blocks UPDATE and DELETE without a key in the WHERE. It lasts for this session only.
7. Update one row by its key
   • Command: UPDATE staff SET city = 'solapur' WHERE id = 4;
   • You should see: Query OK, 1 row affected
   • Check: SELECT city FROM staff WHERE id = 4; → solapur
   • Why: The primary key in WHERE guarantees exactly one row changes, so safe mode allows it.
8. See the gap after a delete
   • Command: DELETE FROM staff WHERE id = 4; INSERT INTO staff (name, city) VALUES ('sara', 'goa');
   • You should see: Query OK, 1 row affected twice.
   • Check: SELECT id, name FROM staff; shows ids 1, 2, 3, 5.
   • Why: AUTO_INCREMENT never reuses a number, so a deleted id stays a gap. Reusing it could mix up old and new records.
9. Empty it, then remove it
   • Command: TRUNCATE TABLE staff; DROP TABLE staff;
   • You should see: Query OK, 0 rows affected twice.
   • Check: DESC staff; → ERROR 1146 (42S02): Table 'instagram.staff' doesn't exist (your database name may differ).
   • Why: TRUNCATE empties the table and resets the counter; DROP removes the table itself. Neither has an undo, so keep them for practice tables.

18. Try at home

Recap

Quick revision cards

Revise this lesson in two minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens its lesson.

More cards: Ravi Sir's Cards.