Learning Objectives – after this chapter you will be able to:
- Alter a table: add, modify and drop columns and constraints; enable/disable constraints.
- Drop a table safely with
CASCADE CONSTRAINTS. - Change data with
UPDATEandDELETE. - Control transactions with
COMMIT,ROLLBACKandSAVEPOINT. - Explain Oracle’s row-level locking and read consistency.
ALTER TABLE
ALTER TABLE tablename [ADD (column specification | table constraint)] [MODIFY (column specification)] [DROP CONSTRAINT name [CASCADE] | DROP PRIMARY KEY [CASCADE] | DROP COLUMN column [CASCADE CONSTRAINTS]] [SET UNUSED COLUMN column] [DROP UNUSED COLUMNS] [ENABLE | DISABLE CONSTRAINT name]Adding a column or constraint
Adding columns and constraints
alter table courses add (cat varchar2(5)); alter table courses add (cat varchar2(5) not null); -- ORA-01758: table must be empty to add mandatory (NOT NULL) column alter table courses add (cat varchar2(5) default 'prog' not null); -- works alter table courses add (constraint courses_cat_chk check (length(cat) >= 2));
- A new column is filled with NULL in existing rows, so a NOT NULL column can be added only if the table is empty or a DEFAULT value is given.
- No constraint can be added that existing data does not satisfy.
- For existing columns only NOT NULL can be added at column level; other constraints are added as table constraints.
Modifying a column
- Increase the width – always allowed:
alter table courses modify (cat varchar2(10)); - Decrease the width or change the datatype – only when the column is empty (no row has a value).
Dropping constraints and columns
Dropping constraints and columns
alter table courses drop constraint courses_cat_chk; alter table courses drop primary key cascade; -- also drops referencing FKs alter table courses drop column cat; -- drop immediately alter table faculty drop column fcode cascade constraints; alter table courses set unused column cat; -- mark for dropping alter table courses drop unused columns; -- physically remove later
- A PRIMARY KEY / UNIQUE constraint referenced by a foreign key can be dropped only with CASCADE.
- DROP COLUMN was introduced in Oracle8i. A referenced key column needs CASCADE CONSTRAINTS; the index created for a PRIMARY KEY/UNIQUE column is dropped automatically.
- SET UNUSED marks a column as dropped (it can no longer be accessed) so the slow physical removal can be done later with DROP UNUSED COLUMNS when system load is low. Unused columns still count towards the 1000-column limit. See
USER_UNUSED_COL_TABSandUSER_TAB_COLUMNS.
Enabling and disabling constraints
Disable while bulk-loading, enable afterwards
alter table courses disable primary key; alter table courses disable constraint courses_cat_chk; alter table courses enable constraint courses_cat_chk;
A disabled constraint is not enforced – useful when inserting a large number of rows. Use CASCADE to disable dependent constraints. The STATUS column of USER_CONSTRAINTS shows whether a constraint is enabled.
DROP TABLE
DROP TABLE tablename [CASCADE CONSTRAINTS];- Removes the data and the structure; it cannot be undone.
- If other tables reference its primary/unique keys, Oracle shows an error unless CASCADE CONSTRAINTS is given (which drops those referencing constraints).
- Views and synonyms based on the table become invalid but remain in the system.
UPDATE and DELETE
Changing and removing rows
update courses set fee = 6000 where ccode = 'asp'; update courses set fee = 6000, duration = 30 where ccode = 'asp'; delete from courses where ccode = 'c';
Without a WHERE clause, UPDATE changes and DELETE removes every row of the table – usually an error, not an intention. A parent row with child rows cannot be deleted unless the foreign key has ON DELETE CASCADE.
Transactions
A transaction is a collection of logically related statements that perform a single task – it must be done completely or not at all (all or nothing). Example: faculty kl leaves, so his batches go to jc:
One task = one transaction
update batches set fcode = 'jc' where fcode = 'kl'; delete from course_faculty where fcode = 'kl'; delete from faculty where fcode = 'kl'; commit; -- or ROLLBACK to undo all three
- A transaction begins when the session begins or when the previous transaction ends, and ends with COMMIT or ROLLBACK. Logging on with SQL*Plus starts a session; EXIT ends it.
- COMMIT – makes changes permanent, releases row locks, ends the transaction and erases its savepoints.
- Implicit commit – before and after every DDL command, and at normal disconnect (EXIT). Never mix DML and DDL in one task.
- ROLLBACK – undoes the changes of the current transaction, ends it, releases locks and erases savepoints.
- Rollback segment – the area where Oracle keeps the old data needed for rollback (and for read consistency). Each transaction is assigned one; the DBA can create more with CREATE ROLLBACK SEGMENT.
SAVEPOINT
Rolling back part of a transaction
update ...; savepoint s1; insert ...; insert ...; savepoint s2; delete ...; rollback to s1; -- undoes both INSERTs and the DELETE, keeps the first UPDATE update ...; commit; -- commits the first and the last UPDATE
Had the command been ROLLBACK TO s2, only the DELETE would have been undone.
Locking
Without locking, two users updating the same row can corrupt data (the “lost update” example with QOH = 20 → sale −5 → purchase +10 → rollback of the sale leaves 20 instead of 30). Oracle prevents this:
- Locking is automatic and transparent.
- It is row-level: only the row being updated is locked (some smaller DBMSs use page-level locking; table-level also exists).
- Other users can still read the row but see only the committed (old) value; uncommitted changes are visible only to the transaction that made them.
- A transaction trying to update a locked row waits indefinitely until the lock is released by COMMIT or ROLLBACK.
- A table can be locked manually with
LOCK TABLE.
Read Consistency
A query returns data consistent with the moment it started. If USER1 starts reading a 20,000-row SALES table at 10:00 and USER2 changes and commits row 15,000 at 10:01, USER1 still receives the 10:00 version of that row when it reaches it. Readers take no locks; Oracle supplies the earlier data from the rollback segment.
Key Takeaways
- ALTER TABLE changes structure; DROP TABLE removes data and structure permanently.
- DML changes are permanent only after COMMIT (explicit or implicit via DDL/EXIT).
- SAVEPOINT allows partial rollback; locks are row-level and released at COMMIT/ROLLBACK.
- Read consistency gives every query a snapshot as of its start time.
Chapter 5 Notes (PDF)
ch5.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 5 notes (PDF). Click a question to see the answer.
Answer : Adding a new column, adding a new table-level constraint, increasing and decreasing the width of a column, changing the datatype of a column, dropping a column, dropping a constraint, disabling a constraint and enabling a disabled constraint.
Answer : Oracle initialises the new column with NULL in all existing rows, which violates NOT NULL, giving ORA-01758 (table must be empty to add mandatory column). It is possible only when the table is empty or when a DEFAULT value is specified, e.g. ADD (cat varchar2(5) DEFAULT 'prog' NOT NULL).
Answer : Only NOT NULL can be added to an existing column at column level, so a CHECK constraint is added as a table constraint, e.g. ALTER TABLE courses ADD (CONSTRAINT courses_cat_chk CHECK (length(cat) >= 2)).
Answer : The length of a column can always be increased. The length can be decreased and the datatype changed only when the column is empty (no row has a value for it). A NOT NULL constraint can be added; other constraints must be added at table level.
Answer : Use ALTER TABLE table DROP CONSTRAINT constraint_name. The name of the constraint is required and can be obtained from the USER_CONSTRAINTS data dictionary view.
Answer : A UNIQUE or PRIMARY KEY constraint that is referenced by a foreign key cannot be dropped without also dropping the foreign key. The CASCADE option (e.g. ALTER TABLE courses DROP PRIMARY KEY CASCADE) drops it along with the dependent REFERENCES constraints.
Answer : A column can be dropped immediately with DROP COLUMN, removing its data at once. Or it can be marked with SET UNUSED COLUMN – it is treated as dropped and cannot be accessed, but its data remains until it is physically removed later with DROP UNUSED COLUMNS, which is useful when system load is low.
Answer : When a constraint is disabled Oracle does not enforce its rule; this is useful when inserting a large number of rows quickly. It is done with ALTER TABLE ... DISABLE CONSTRAINT name (CASCADE for dependent constraints) and re-enabled with ENABLE CONSTRAINT. The STATUS column of USER_CONSTRAINTS shows the status.
Answer : DROP TABLE removes both the data and the structure of a table and cannot be undone; views and synonyms based on it become invalid. CASCADE CONSTRAINTS drops the constraints of other tables that refer to the primary/unique keys of the dropped table; without it Oracle shows an error if such references exist.
Answer : All rows of the table are affected – UPDATE changes every row and DELETE removes every row. This is more often the result of an error than intentional.
Answer : A transaction is a collection of logically related statements that perform a single task; either all of them are done or none (all or nothing). It begins when the session begins or when the previous transaction ends, and ends when COMMIT or ROLLBACK is issued.
Answer : COMMIT makes all changes of the transaction permanent, unlocks the rows locked during the transaction, ends the transaction and erases its savepoints. Oracle commits implicitly before and after a DDL command and at normal disconnect from the database (e.g. EXIT from SQL*Plus).
Answer : It is the area where Oracle stores the data that existed before a change so that it can be restored when the user rolls back. Every transaction is associated with a rollback segment; Oracle also uses it to provide read consistency.
Answer : SAVEPOINT marks a location in a transaction so that changes can be rolled back up to that mark instead of to the beginning. For example: UPDATE; SAVEPOINT s1; INSERT; INSERT; SAVEPOINT s2; DELETE; ROLLBACK TO s1 – this undoes both INSERTs and the DELETE but keeps the first UPDATE.
Answer : Oracle automatically and transparently locks only the row being updated (row-level locking) until the transaction ends with COMMIT or ROLLBACK; other transactions can read the row but see only committed data, and a transaction trying to update a locked row waits indefinitely. Read consistency ensures that a query returns data consistent to the point the query started, using the rollback segment, even if others change and commit rows meanwhile.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Gray, J., & Reuter, A. (1992). Transaction Processing: Concepts and Techniques. Morgan Kaufmann.
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.