modify create statments

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

[diafol]

AFAIK, you need a constraint and a CASCADE on delete.

http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

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

[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/