Member Avatar for hwoarang69

below, I have created 4 tables using oracle sql. I want to change the code so that when deleting a student will delete all enrollments for that student.

any idea how can I do this?

COURSES table
CREATE TABLE COURSES
  (COURSE VARCHAR(6),
   DESCRIPTION VARCHAR(60),
   CREDITS INT,
   CONSTRAINT pk PRIMARY KEY (COURSE));
OFFERINGS table
CREATE TABLE OFFERINGS
( CRN INT,
  COURSE VARCHAR(6),
  SECTION VARCHAR(2),
  DATES VARCHAR(5),
  TIMES VARCHAR(30),
  INSTRUCTOR VARCHAR(30),
  ROOM VARCHAR(20),
  ENROLLMAX INT,
  ENROLLCURRENT INT,
  CONSTRAINT pk2 PRIMARY KEY (CRN),
  CONSTRAINT fk1 FOREIGN KEY (COURSE) REFERENCES COURSES (COURSE));
STUDENTS table
CREATE TABLE STUDENTS
( USERID VARCHAR(9),
  PASSWORD VARCHAR(10),
  FIRSTNAME VARCHAR(20),
  LASTNAME VARCHAR(30),
  PHONE VARCHAR(12),
  DOB VARCHAR(40),
  MAJOR VARCHAR(20),
  ADDRESS VARCHAR(30),
  CITY VARCHAR(30),
  STATE VARCHAR(2),
  ZIP VARCHAR(5),
  CONSTRAINT pk3 PRIMARY KEY (USERID));
ENROLLMENTS table
CREATE TABLE ENROLLMENTS
( CRN INT,
  USERID VARCHAR(9),
  ENROLLDATE VARCHAR(30),
  GRADE VARCHAR(2),
  CONSTRAINT pk4 PRIMARY KEY(CRN, USERID),
  CONSTRAINT fk2 FOREIGN KEY (CRN) REFERENCES OFFERINGS(CRN),
  CONSTRAINT fk3 FOREIGN KEY (USERID) REFERENCES STUDENTS(USERID));
  • 2 Contributors
  • 3 Replies
  • 243 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short answer: add ON DELETE CASCADE to the foreign key from ENROLLMENTS(USERID) → STUDENTS(USERID). was right about using a cascade; your attempt put the clause in the wrong place. The cascade must be part of the foreign-key definition (or added later with ALTER TABLE). Below are safe, platform-specific steps plus a note about a common gotcha in your schema.

For an existing Oracle constraint (drop then add with cascade):

ALTER TABLE ENROLLMENTS DROP CONSTRAINT fk3;

ALTER TABLE ENROLLMENTS
  ADD CONSTRAINT fk3
  FOREIGN KEY (USERID)
  REFERENCES STUDENTS(USERID) ON DELETE CASCADE;

For MySQL (find the actual FK name if it differs, then drop/add):

SHOW CREATE TABLE ENROLLMENTS;
ALTER TABLE ENROLLMENTS DROP FOREIGN KEY fk3;

ALTER TABLE ENROLLMENTS
  ADD CONSTRAINT fk3
  FOREIGN KEY (USERID) REFERENCES STUDENTS(USERID)
  ON DELETE CASCADE;

Important caution: ON DELETE CASCADE will remove ENROLLMENTS rows automatically, but it will NOT update your OFFERINGS.ENROLLCURRENT column. To keep ENROLLCURRENT accurate you can either stop storing a derived count and compute it with COUNT(*) or use an AFTER DELETE trigger to decrement the counter. Example triggers (adjust names/types to match your DB):

-- Oracle
CREATE OR REPLACE TRIGGER trg_enroll_after_delete
AFTER DELETE ON ENROLLMENTS
FOR EACH ROW
BEGIN
  UPDATE OFFERINGS
  SET ENROLLCURRENT = CASE WHEN ENROLLCURRENT > 0 THEN ENROLLCURRENT - 1 ELSE 0 END
  WHERE CRN = :OLD.CRN;
END;
/
-- MySQL
DELIMITER $$
CREATE TRIGGER trg_enroll_after_delete
AFTER DELETE ON ENROLLMENTS
FOR EACH ROW
BEGIN
  UPDATE OFFERINGS
  SET ENROLLCURRENT = IF(ENROLLCURRENT > 0, ENROLLCURRENT - 1, 0)
  WHERE CRN = OLD.CRN;
END$$
DELIMITER ;

Test in a transaction or on a copy of the schema, verify FK names (MySQL often auto-names them), and back up before changes. If you rely on ENROLLCURRENT for business rules, prefer deriving it from ENROLLMENTS or use strict transactional updates to avoid race conditions.

Recommended Answers

All 3 Replies

Member Avatar for hwoarang69

thanks, so when deleting a student will delete all enrollments for that student?

CREATE TABLE ENROLLMENTS
( CRN INT,
  USERID VARCHAR(9),
  ENROLLDATE VARCHAR(30),
  GRADE VARCHAR(2),
  CONSTRAINT pk4 PRIMARY KEY(CRN, USERID),
  CONSTRAINT fk2 FOREIGN KEY (CRN) REFERENCES OFFERINGS(CRN),
  CONSTRAINT fk3 FOREIGN KEY (USERID) REFERENCES STUDENTS(USERID))
  ON DELETE CASCADE;
Edited by hwoarang69
Member Avatar for Member #120589

Have you tried it?

//EDIT

Apologies - you posted in MySQL but your other thread was pertaining to Oracle. I made the wrong assumption. This may therefore be more useful:

http://www.mysqltutorial.org/mysql-on-delete-cascade/

Edited by Member #120589
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.