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
- Use
ALTER TABLEto add, modify, rename and drop columns and constraints. - Distinguish
DROP,DELETEandTRUNCATEprecisely. - Write
INSERT,UPDATEandDELETEin all their forms. - Predict the effect of each referential action on a cascading change.
- 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:
- Run the
WHEREas aSELECTfirst.SELECT * FROM STUDENT WHERE Rollno='X2019';— check that exactly the intended rows come back. - Wrap it in a transaction.
START TRANSACTION; UPDATE ...; SELECT ...; -- COMMIT or ROLLBACK - Enable safe-update mode. In MySQL,
SET SQL_SAFE_UPDATES = 1;refuses anyUPDATEorDELETEthat 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';
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);
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'));
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:
-
Run the
WHEREclause as aSELECTfirst. Before anyUPDATEorDELETE, executeSELECT * FROM STUDENT WHERE <condition>;and confirm that exactly the intended rows are returned. This costs seconds and catches almost every such error. -
Wrap the statement in an explicit transaction.
Nothing is permanent untilSTART TRANSACTION; UPDATE STUDENT SET Name='Ankit Kumar Sharma' WHERE Rollno='X2019'; SELECT * FROM STUDENT; -- inspect ROLLBACK; -- or COMMIT if correctCOMMIT, so a mistake costs nothing. -
Enable safe-update mode.
SET SQL_SAFE_UPDATES = 1;makes MySQL refuse anyUPDATEorDELETEthat 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;
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
- Which sub-language does
TRUNCATEbelong to, and why? - Can
TRUNCATEtake aWHEREclause? - Why must child tables be dropped before parent tables?
- What happens to existing rows when a column is added without a
DEFAULT? - Name three defences against a
DELETEwith noWHERE. - What does
ON UPDATE CASCADEdo?
B. Write the SQL
- Add a column
Email VARCHAR(60)toSTUDENT, unique and nullable. - Change
CnametoVARCHAR(60). - Rename
TeacherInChargetoTIC. - Add a check that
Durationis between 1 and 6. - Drop that check.
- Add a foreign key from
ENROLLMENT.SocIDtoSOCIETY.SocIDwith cascade on both actions. - Insert five societies and six enrolments.
- Increase
TotalSeatsby 15 per cent for full-time courses only. - Set
FeesPaidto'Yes'for every student enrolled in the Debating society. - Delete all enrolments in societies with fewer than three members.
- Copy every student born in 2001 into a new table
SENIOR_STUDENTS. - Advance all enrolment dates by six months.
C. Predict the outcome
- With
fk_adm_student ON DELETE CASCADEandfk_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'); STUDENThas 400 rows, of which 3 haveNamelonger than 20 characters. What happens onALTER TABLE STUDENT MODIFY Name VARCHAR(20);? How would you find the problem rows first?COURSEhas a row withTotalSeats = 0. What happens onALTER TABLE COURSE ADD CONSTRAINT chk CHECK (TotalSeats > 0);? How do you proceed?
D. Analytical
- Argue for and against declaring
ON DELETE CASCADEon every foreign key. Recommend a policy. - Explain why
INSERT ... SELECTinto a table with a primary key often needsDISTINCT. Give a concrete failing example. - A table has
AUTO_INCREMENTat 500. Compare the state afterDELETE FROM t;and afterTRUNCATE TABLE t;, and say when each behaviour is desirable. - Write a safe procedure, as a numbered list, for renaming a column that is used by two views and one application.
- Explain why
ALTER TABLE ... ADD CONSTRAINTvalidates existing data, and what happens if validation fails.
E. Objective — True or False with reason
DELETEcan be rolled back.TRUNCATEfires delete triggers.DROP COLUMNcan be undone withADD COLUMN.ON DELETE SET NULLcan be used on a foreign key that is part of the primary key.UPDATEwithoutWHEREupdates one row.- Adding a column to a table with a million rows is instantaneous in all systems.
Answers to Section E
- True. It is DML, logged row by row, and participates in the transaction.
- False. It does not fire row triggers, because it deallocates pages rather than deleting rows individually — a real hazard if triggers maintain audit tables.
- False.
ADD COLUMNrecreates the column but not its data, which was destroyed. - False. Entity integrity forbids NULL in any part of a primary key.
- False. It updates every row in the table.
- 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 TABLEperforms 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) andDELETE(selective, logged, rollback-able) are three different operations.- Prefer named-column
INSERT, and the multi-row form for bulk loads.INSERT ... SELECTcopies query results — usually needingDISTINCT. - Referential actions determine whether a parent change cascades, is blocked, or nulls the children. Default to
RESTRICT. - The three disasters —
UPDATE/DELETEwithoutWHERE, and over-broadCASCADE— are prevented by select first, transaction second, safe-update mode third.