0

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
15
Views
2 Years
Discussion Span
Last Post by diafol
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.