Skip to content

Lesson 14: ALTER, DROP, Update Behaviours and DML

Changing the schema, and changing the data

About this lesson

Course: Database Management Systems

Unit 4: Structured Query Language · 12 Lecture Hours

Lesson duration: 2 lectures

Reading: Elmasri & Navathe, Ch. 6.2, 6.4 · Murach's MySQL, Ch. 5, 11

Learning Outcomes

  1. Use ALTER TABLE to add, modify, rename and drop columns and constraints.
  2. Distinguish DROP, DELETE and TRUNCATE precisely.
  3. Write INSERT, UPDATE and DELETE in all their forms.
  4. Predict the effect of each referential action on a cascading change.
  5. Avoid the three classic DML disasters.

14.1 ALTER TABLE

ALTER TABLE performs schema evolution (Unit 1, Lesson 2) — the operation that program–data independence is designed to make painless.

Adding a column

ALTER TABLE STUDENT ADD COLUMN Mobile CHAR(10) DEFAULT '9999999999';

This is course practical question 23. Every existing row immediately acquires the default value. Without a DEFAULT, existing rows get NULL — which fails if you also specify NOT NULL and the table is non-empty.

ALTER TABLE ENROLLMENT ADD COLUMN FeesPaid VARCHAR(3) DEFAULT 'No'
    CHECK (FeesPaid IN ('Yes','No'));

This is course practical question 19.

Several columns at once:

ALTER TABLE STUDENT
    ADD COLUMN Email   VARCHAR(60),
    ADD COLUMN Address VARCHAR(100);

Modifying a column

ALTER TABLE STUDENT MODIFY COLUMN Name VARCHAR(60) NOT NULL;   -- MySQL
ALTER TABLE STUDENT ALTER COLUMN Name TYPE VARCHAR(60);        -- PostgreSQL

Widening is safe; narrowing is not

VARCHAR(40) → VARCHAR(60) always succeeds. VARCHAR(40) → VARCHAR(20) fails or truncates if any existing value is longer. Always check first:

SELECT MAX(LENGTH(Name)) FROM STUDENT;

Renaming

ALTER TABLE COURSE RENAME COLUMN Cname TO CourseName;   -- column
ALTER TABLE COURSE RENAME TO SUBJECT;                   -- table

Renaming is a conceptual schema change and will break any view or application that names the old identifier — the limit of logical data independence (Unit 1, Lesson 3).

Dropping a column

ALTER TABLE STUDENT DROP COLUMN Mobile;

This destroys the data in that column irrecoverably. It fails if the column is referenced by a foreign key or used in a constraint.

Adding and dropping constraints

ALTER TABLE COURSE ADD CONSTRAINT chk_seats CHECK (TotalSeats > 0);

ALTER TABLE ADMISSION ADD CONSTRAINT fk_adm_course
    FOREIGN KEY (SID) REFERENCES COURSE(SID) ON DELETE RESTRICT;

ALTER TABLE COURSE DROP CONSTRAINT chk_seats;      -- standard
ALTER TABLE COURSE DROP CHECK chk_seats;           -- MySQL
ALTER TABLE ADMISSION DROP FOREIGN KEY fk_adm_course;
ALTER TABLE COURSE DROP PRIMARY KEY;

Adding a constraint validates the existing data first; if any row violates it, the ALTER is rejected and nothing changes. This is why naming constraints at creation time (Lesson 1) pays off — you cannot drop what you cannot name.

14.2 DROP, DELETE and TRUNCATE

The comparison examiners ask for every year

DROP TABLE TRUNCATE TABLE DELETE FROM
Category DDL DDL DML
Removes Rows and the table definition All rows; keeps the table Rows matching WHERE (all if omitted)
WHERE clause No No Yes
Speed Fast Very fast — deallocates pages Slow — row by row, logged
Rollback Generally no Generally no Yes
Fires triggers No No Yes
Resets AUTO_INCREMENT n/a Yes No
Afterwards Table gone Empty table, structure intact Table with remaining rows
DROP TABLE IF EXISTS ADMISSION;        -- table and data gone
TRUNCATE TABLE ADMISSION;              -- empty, structure kept
DELETE FROM ADMISSION WHERE SID='CS01';-- selective, rollback-able

Drop order. A table referenced by a foreign key cannot be dropped. Drop children before parents:

DROP TABLE IF EXISTS ENROLLMENT, ADMISSION;   -- children first
DROP TABLE IF EXISTS SOCIETY, COURSE, STUDENT;-- then parents

14.3 INSERT

Form 1 — all columns, in order

INSERT INTO STUDENT VALUES ('X2019', 'Ankit Sharma', '2001-03-14');

Fragile: it breaks silently if the column order changes.

Form 2 — named columns (always prefer this)

INSERT INTO STUDENT (Rollno, Name, Dateofbirth)
VALUES ('X2020', 'Riya Verma', '2002-05-11');

Robust against column reordering, and lets you omit columns that have defaults or accept NULL.

Form 3 — multiple rows

INSERT INTO COURSE (SID, Cname, TotalSeats, Duration, Coursetype, TeacherInCharge)
VALUES ('CS01','Computer Science',60,3,'Fulltime','R. K. Gupta'),
       ('CH01','Chemistry',       45,3,'Fulltime','S. Menon'),
       ('MA02','Mathematics',     50,3,'Parttime','A. Gupta'),
       ('EN03','English',         40,3,'Parttime','P. Das'),
       ('PH01','Physics',         40,3,'Fulltime','V. Rao');

Far faster than five separate statements: one parse, one transaction, one round trip.

Form 4 — INSERT ... SELECT

CREATE TABLE PARTTIME_STUDENTS (Rollno VARCHAR(8), Name VARCHAR(40));

INSERT INTO PARTTIME_STUDENTS (Rollno, Name)
SELECT DISTINCT s.Rollno, s.Name
FROM   STUDENT s
JOIN   ADMISSION a ON s.Rollno = a.Rollno
JOIN   COURSE    c ON a.SID    = c.SID
WHERE  c.Coursetype = 'Parttime';

Copies the result of a query into a table. The SELECT list must match the target columns in number and domain.

Handling duplicates

INSERT IGNORE INTO STUDENT VALUES ('X2019','Duplicate','2001-01-01');  -- skip silently

INSERT INTO STUDENT (Rollno, Name, Dateofbirth)
VALUES ('X2019','Ankit Kumar Sharma','2001-03-14')
ON DUPLICATE KEY UPDATE Name = VALUES(Name);                           -- "upsert"

14.4 UPDATE

UPDATE table_name
SET    column1 = expression1, column2 = expression2
WHERE  condition;

Course question 7 — modify a student's name:

UPDATE STUDENT SET Name = 'Ankit Kumar Sharma' WHERE Rollno = 'X2019';

Course question 18 — increase every course's total seats by 10 per cent:

UPDATE COURSE SET TotalSeats = CEIL(TotalSeats * 1.10);

CEIL is used because TotalSeats is an integer and a fractional seat is meaningless; rounding up is the generous interpretation. Note that this statement has no WHERE — deliberately, because every course is to change.

Course question 20 — advance every admission date by one year:

UPDATE ADMISSION SET Dateofadmission = DATE_ADD(Dateofadmission, INTERVAL 1 YEAR);

Conditional update with CASE:

UPDATE COURSE
SET TotalSeats = CASE
        WHEN Coursetype = 'Fulltime' THEN TotalSeats + 10
        WHEN Coursetype = 'Parttime' THEN TotalSeats + 5
        ELSE TotalSeats
    END;

Update driven by another table:

UPDATE COURSE c
SET    c.TotalSeats = c.TotalSeats + 20
WHERE  c.SID IN (SELECT a.SID FROM ADMISSION a
                 GROUP BY a.SID HAVING COUNT(*) > 1);

Disaster 1 — the forgotten WHERE

UPDATE STUDENT SET Name = 'Ankit Kumar Sharma';   -- renames EVERY student

Every row is updated. Three defences:

  1. Run the WHERE as a SELECT first. SELECT * FROM STUDENT WHERE Rollno='X2019'; — check that exactly the intended rows come back.
  2. Wrap it in a transaction. START TRANSACTION; UPDATE ...; SELECT ...; -- COMMIT or ROLLBACK
  3. Enable safe-update mode. In MySQL, SET SQL_SAFE_UPDATES = 1; refuses any UPDATE or DELETE that does not restrict on a key.

14.5 DELETE

DELETE FROM ADMISSION WHERE Rollno = 'X2019' AND SID = 'MA02';   -- one row
DELETE FROM ADMISSION WHERE SID = 'EN03';                        -- several
DELETE FROM ADMISSION;                                           -- ALL rows

Delete driven by a subquery:

DELETE FROM ADMISSION
WHERE  SID IN (SELECT SID FROM COURSE WHERE TotalSeats < 30);

Disaster 2 — DELETE without WHERE

DELETE FROM STUDENT; empties the table. Worse, with ON DELETE CASCADE declared, it also empties ADMISSION and ENROLLMENT. One statement, three tables gone. The same three defences apply.

14.6 Update Behaviours — Referential Actions in Practice

Unit 3, Lesson 2 defined the five referential actions. Here is what they actually do.

Setup:

STUDENT:   ('X2019','Ankit Sharma','2001-03-14')
ADMISSION: ('X2019','CS01','2019-07-15'), ('X2019','MA02','2019-08-01')

fk_adm_student : ON DELETE CASCADE  ON UPDATE CASCADE
fk_adm_course  : ON DELETE RESTRICT ON UPDATE CASCADE

Case 1 — delete the student.

DELETE FROM STUDENT WHERE Rollno = 'X2019';

ON DELETE CASCADE → the student row and both admission rows are deleted. Result: ADMISSION loses two rows.

Case 2 — delete a course that has admissions.

DELETE FROM COURSE WHERE SID = 'CS01';

ON DELETE RESTRICT → rejected. Error: cannot delete or update a parent row: a foreign key constraint fails. Nothing changes. To proceed, the admissions must first be removed or transferred.

Case 3 — change a roll number.

UPDATE STUDENT SET Rollno = 'X9019' WHERE Rollno = 'X2019';

ON UPDATE CASCADE → the student row changes and both admission rows are rewritten to X9019. The link is preserved.

Case 4 — the same, but with RESTRICT on update.

The UPDATE would be rejected, because children still reference the old value.

   ON DELETE CASCADE                ON DELETE RESTRICT

   STUDENT                          COURSE
   +--------+                       +------+
   | X2019  |  DELETE               | CS01 |  DELETE
   +---+----+                       +---+--+
       | cascades                       | blocked
       v                                X
   ADMISSION                        ADMISSION
   +--------+------+                +--------+------+
   | X2019  | CS01 | deleted        | X2019  | CS01 | preserved
   | X2019  | MA02 | deleted        +--------+------+
   +--------+------+                     ERROR 1451 raised

Disaster 3 — cascading further than you meant

With ON DELETE CASCADE declared on every foreign key in a ten-table schema, deleting one STUDENT row can propagate through admissions, enrolments, fee receipts, examination results and library issues. One statement, most of the database gone.

Policy: default to RESTRICT. Use CASCADE only where the child is genuinely existence-dependent on the parent (a weak entity, an order line), and never on a table that constitutes an audit trail.

14.7 Solved Questions

Solved Question 1

Q. Distinguish DROP, DELETE and TRUNCATE on at least six points, with an example of when each is correct.

A. (Table of Section 2.2.) The essential points:

Category. DROP and TRUNCATE are DDL; DELETE is DML. This single fact explains most of the other differences.

What survives. DROP TABLE removes the rows and the table definition — the table ceases to exist. TRUNCATE removes all rows but leaves an empty table with its structure, constraints and privileges intact. DELETE removes only the rows matching WHERE.

Selectivity. Only DELETE accepts a WHERE clause.

Speed and logging. DELETE removes rows one at a time, logging each for rollback — slow on a large table. TRUNCATE deallocates the data pages wholesale, so it is nearly instantaneous regardless of size.

Rollback and triggers. DELETE can be rolled back and fires row-level triggers. TRUNCATE generally cannot be rolled back and fires no triggers — which matters if triggers maintain audit tables.

AUTO_INCREMENT. TRUNCATE resets the counter to 1; DELETE does not.

When each is correct:

  • DELETE FROM ADMISSION WHERE SID='EN03'; — removing selected rows in a live system.
  • TRUNCATE TABLE ADMISSION; — clearing a staging or test table completely before reloading.
  • DROP TABLE ADMISSION; — the table is no longer part of the design.

Solved Question 2

Q. Write SQL for course practicals 7, 18, 19, 20 and 23, explaining each.

A.

Q7 — modify a student's name.

UPDATE STUDENT SET Name = 'Ankit Kumar Sharma' WHERE Rollno = 'X2019';
The WHERE on the primary key guarantees exactly one row is affected. Omitting it would rename every student.

Q18 — increment total seats of each course by 10 per cent.

UPDATE COURSE SET TotalSeats = CEIL(TotalSeats * 1.10);
No WHERE, deliberately — every course changes. CEIL is used because TotalSeats is an integer and a fractional seat is meaningless; ROUND would be acceptable if the intent were nearest-integer. Note the CHECK (TotalSeats > 0) constraint is re-validated and still holds.

Q19 — add an enrolment-fees-paid field.

ALTER TABLE ENROLLMENT
    ADD COLUMN FeesPaid VARCHAR(3) NOT NULL DEFAULT 'No'
    CHECK (FeesPaid IN ('Yes','No'));
The DEFAULT 'No' is essential: it gives every existing row a valid value, so NOT NULL can be imposed on a non-empty table. The CHECK restricts the domain to the two permitted values.

Q20 — update the date of admission for all courses by one year.

UPDATE ADMISSION SET Dateofadmission = DATE_ADD(Dateofadmission, INTERVAL 1 YEAR);
DATE_ADD with an INTERVAL handles leap years correctly, which naive arithmetic on a string would not.

Q23 — add a mobile-number column with a default.

ALTER TABLE STUDENT ADD COLUMN Mobile CHAR(10) DEFAULT '9999999999';
CHAR(10) rather than VARCHAR(10) because an Indian mobile number is exactly ten digits — a genuinely fixed length. It is stored as a string, not a number, because it is an identifier rather than a quantity: leading zeros must be preserved and no arithmetic is ever performed on it.

Solved Question 3

Q. Given the setup of Section 2.6, state the exact effect of each statement.

a. DELETE FROM STUDENT WHERE Rollno='X2019'; b. DELETE FROM COURSE WHERE SID='CS01'; c. UPDATE STUDENT SET Rollno='X9019' WHERE Rollno='X2019'; d. UPDATE COURSE SET SID='CS99' WHERE SID='CS01'; e. DELETE FROM ADMISSION WHERE Rollno='X2019'; then DELETE FROM COURSE WHERE SID='CS01';

A.

a. fk_adm_student is ON DELETE CASCADE. The STUDENT row is deleted and both admission rows (CS01, MA02) are deleted automatically. Three rows removed in total by one statement.

b. fk_adm_course is ON DELETE RESTRICT. The admission ('X2019','CS01') still references CS01, so the delete is rejected with a foreign-key error. Nothing changes — neither the course nor the admissions.

c. fk_adm_student is ON UPDATE CASCADE. The student's key changes to X9019, and both admission rows are rewritten to X9019. The relationship is preserved. Note this is possible only because the DBMS temporarily suspends the constraint check during the cascade.

d. fk_adm_course is ON UPDATE CASCADE. The course key becomes CS99, and the admission row ('X2019','CS01') becomes ('X2019','CS99'). Succeeds — the difference from (b) is that update cascades were permitted while delete cascades were not.

e. The first statement deletes both of X2019's admissions, so nothing references CS01 any more. The second then succeeds, because RESTRICT only blocks when a referencing row exists. This is the correct manual procedure that RESTRICT is designed to force upon the administrator — deal with the children explicitly rather than have them silently destroyed.

Solved Question 4

Q. Write INSERT statements to populate STUDENT and COURSE, then copy all part-time students into a new table using INSERT ... SELECT.

A.

INSERT INTO STUDENT (Rollno, Name, Dateofbirth) VALUES
  ('X2019','Ankit Sharma','2001-03-14'),
  ('X2020','Riya Verma',  '2002-05-11'),
  ('Z1188','Sana Iqbal',  '2001-11-02'),
  ('Z2049','Arjun Nair',  '2003-01-25'),
  ('X3001','Aditi Rao',   '2001-07-09');

INSERT INTO COURSE (SID, Cname, TotalSeats, Duration, Coursetype, TeacherInCharge) VALUES
  ('CS01','Computer Science',60,3,'Fulltime','R. K. Gupta'),
  ('CH01','Chemistry',       45,3,'Fulltime','S. Menon'),
  ('MA02','Mathematics',     50,3,'Parttime','A. Gupta'),
  ('EN03','English',         40,3,'Parttime','P. Das'),
  ('PH01','Physics',         40,3,'Fulltime','V. Rao');

CREATE TABLE PARTTIME_STUDENTS (
    Rollno VARCHAR(8) PRIMARY KEY,
    Name   VARCHAR(40) NOT NULL
);

INSERT INTO PARTTIME_STUDENTS (Rollno, Name)
SELECT DISTINCT s.Rollno, s.Name
FROM   STUDENT s
JOIN   ADMISSION a ON s.Rollno = a.Rollno
JOIN   COURSE    c ON a.SID    = c.SID
WHERE  c.Coursetype = 'Parttime';

Three points. The multi-row form is used because it is one parse and one transaction rather than five, so it is markedly faster. Columns are named explicitly so the statements survive a later column reordering. And DISTINCT is essential in the INSERT ... SELECT: a student admitted to two part-time courses would otherwise produce two rows and violate the primary key of the target table.

Solved Question 5

Q. A junior DBA runs UPDATE STUDENT SET Name='Ankit'; and then DELETE FROM STUDENT;. Describe the damage and give three defences.

A.

Damage from the UPDATE. Every row of STUDENT now has the name 'Ankit'. All original names are gone, and no constraint was violated, so no error was raised. If the statement was auto-committed, the only recovery is a restore from backup.

Damage from the DELETE. Every student row is removed. Because fk_adm_student and fk_enr_student are declared ON DELETE CASCADE, ADMISSION and ENROLLMENT are also emptied. One statement has destroyed three tables. (Had RESTRICT been declared, the delete would have been blocked — an accidental argument for conservative referential actions.)

Three defences:

  1. Run the WHERE clause as a SELECT first. Before any UPDATE or DELETE, execute SELECT * FROM STUDENT WHERE <condition>; and confirm that exactly the intended rows are returned. This costs seconds and catches almost every such error.

  2. Wrap the statement in an explicit transaction.

    START TRANSACTION;
    UPDATE STUDENT SET Name='Ankit Kumar Sharma' WHERE Rollno='X2019';
    SELECT * FROM STUDENT;          -- inspect
    ROLLBACK;                        -- or COMMIT if correct
    
    Nothing is permanent until COMMIT, so a mistake costs nothing.

  3. Enable safe-update mode. SET SQL_SAFE_UPDATES = 1; makes MySQL refuse any UPDATE or DELETE that does not restrict on a key column. MySQL Workbench enables it by default for exactly this reason.

A fourth, organisational defence: grant ordinary users SELECT and INSERT only, reserving UPDATE and DELETE for accounts used deliberately (Lesson 6) — and take regular, tested backups.

Solved Question 6

Q. Why does adding a NOT NULL column to a populated table sometimes fail? Give the failing statement and two fixes.

A. Adding a column gives every existing row a value for it. If no DEFAULT is specified, that value is NULL — which directly contradicts a NOT NULL declaration. The DBMS therefore rejects the statement rather than create rows that violate their own schema.

Fails on a non-empty table:

ALTER TABLE STUDENT ADD COLUMN Email VARCHAR(60) NOT NULL;
Error: the existing rows would need NULL in a NOT NULL column.

Fix 1 — supply a DEFAULT. Every existing row is filled with it immediately, so no NULL ever exists:

ALTER TABLE STUDENT ADD COLUMN Email VARCHAR(60) NOT NULL DEFAULT 'not-supplied';

Fix 2 — add it nullable, populate, then tighten. Preferable when a sensible universal default does not exist, because it forces real values to be supplied:

ALTER TABLE STUDENT ADD COLUMN Email VARCHAR(60);                 -- nullable
UPDATE STUDENT SET Email = CONCAT(LOWER(Rollno),'@college.edu');  -- populate
ALTER TABLE STUDENT MODIFY COLUMN Email VARCHAR(60) NOT NULL;     -- tighten

The second approach is the standard production pattern, because it separates the schema change from the data migration and lets each be verified independently. Note that the final MODIFY will itself fail if the UPDATE missed any row — which is a useful safety check, not a nuisance.

14.8 Exercises

A. Short answer

  1. Which sub-language does TRUNCATE belong to, and why?
  2. Can TRUNCATE take a WHERE clause?
  3. Why must child tables be dropped before parent tables?
  4. What happens to existing rows when a column is added without a DEFAULT?
  5. Name three defences against a DELETE with no WHERE.
  6. What does ON UPDATE CASCADE do?

B. Write the SQL

  1. Add a column Email VARCHAR(60) to STUDENT, unique and nullable.
  2. Change Cname to VARCHAR(60).
  3. Rename TeacherInCharge to TIC.
  4. Add a check that Duration is between 1 and 6.
  5. Drop that check.
  6. Add a foreign key from ENROLLMENT.SocID to SOCIETY.SocID with cascade on both actions.
  7. Insert five societies and six enrolments.
  8. Increase TotalSeats by 15 per cent for full-time courses only.
  9. Set FeesPaid to 'Yes' for every student enrolled in the Debating society.
  10. Delete all enrolments in societies with fewer than three members.
  11. Copy every student born in 2001 into a new table SENIOR_STUDENTS.
  12. Advance all enrolment dates by six months.

C. Predict the outcome

  1. With fk_adm_student ON DELETE CASCADE and fk_adm_course ON DELETE RESTRICT, and data as in Section 2.6, state the exact effect of: a. DELETE FROM STUDENT; b. DELETE FROM COURSE; c. TRUNCATE TABLE STUDENT; d. DROP TABLE STUDENT; e. UPDATE COURSE SET SID = CONCAT(SID,'X');
  2. STUDENT has 400 rows, of which 3 have Name longer than 20 characters. What happens on ALTER TABLE STUDENT MODIFY Name VARCHAR(20);? How would you find the problem rows first?
  3. COURSE has a row with TotalSeats = 0. What happens on ALTER TABLE COURSE ADD CONSTRAINT chk CHECK (TotalSeats > 0);? How do you proceed?

D. Analytical

  1. Argue for and against declaring ON DELETE CASCADE on every foreign key. Recommend a policy.
  2. Explain why INSERT ... SELECT into a table with a primary key often needs DISTINCT. Give a concrete failing example.
  3. A table has AUTO_INCREMENT at 500. Compare the state after DELETE FROM t; and after TRUNCATE TABLE t;, and say when each behaviour is desirable.
  4. Write a safe procedure, as a numbered list, for renaming a column that is used by two views and one application.
  5. Explain why ALTER TABLE ... ADD CONSTRAINT validates existing data, and what happens if validation fails.

E. Objective — True or False with reason

  1. DELETE can be rolled back.
  2. TRUNCATE fires delete triggers.
  3. DROP COLUMN can be undone with ADD COLUMN.
  4. ON DELETE SET NULL can be used on a foreign key that is part of the primary key.
  5. UPDATE without WHERE updates one row.
  6. Adding a column to a table with a million rows is instantaneous in all systems.

Answers to Section E

  1. True. It is DML, logged row by row, and participates in the transaction.
  2. False. It does not fire row triggers, because it deallocates pages rather than deleting rows individually — a real hazard if triggers maintain audit tables.
  3. False. ADD COLUMN recreates the column but not its data, which was destroyed.
  4. False. Entity integrity forbids NULL in any part of a primary key.
  5. False. It updates every row in the table.
  6. False. Some systems rewrite the whole table, which can lock it for a long time. Others add the column to the catalog only and are near-instant. Check the specific DBMS and version before altering a large production table.

Lesson Summary

  • ALTER TABLE performs schema evolution: add, modify, rename, drop columns and constraints. Widening is safe; narrowing and dropping destroy data.
  • DROP (table gone), TRUNCATE (rows gone, fast, no rollback, no triggers, resets counters) and DELETE (selective, logged, rollback-able) are three different operations.
  • Prefer named-column INSERT, and the multi-row form for bulk loads. INSERT ... SELECT copies query results — usually needing DISTINCT.
  • Referential actions determine whether a parent change cascades, is blocked, or nulls the children. Default to RESTRICT.
  • The three disasters — UPDATE/DELETE without WHERE, and over-broad CASCADE — are prevented by select first, transaction second, safe-update mode third.