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));

Recommended Answers

All 3 Replies

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;
Member Avatar for diafol

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/

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.