on sql Foreign key

Question

Can someone help me in SQL I'm using Oracle SQL*plus

Create the following tables.

Table Student:

stdNo CHAR(5) This is the Primary Key
lastname VARCHAR(25) Must be Not Null
givennames VARCHAR(50) Must be Not Null
Dept CHAR(4)

Table Course:

courseID CHAR(8) This is the Primary Key
courseTitle VARCHAR(50) Must be Unique and Not Null
Cost DECIMAL(6,2) Ensure Cost is greater or equal to zero
Credits INT Ensure that Credits are between 0 and 200. Also the default value is 2

Table Semester:

semesterID CHAR(5) This is the Primary Key
semesterCode INT Ensure that semesterCode is Between 1 and 4
Year INT Ensure that year is Between 2000 and 9999

Table Register:

stdNo CHAR(5) Foreign Key referenced to stdNo in Student table, On update cascade On delete cascade
courseID CHAR(8) Foreign Key referenced to courseID in Course table, On update cascade On delete cascade
semesterID CHAR(5) Foreign Key referenced to semesterID in Semester table, On update cascade On delete cascade
Grade CHAR(2)
Mark DECIMAL(4,2) Mark should be between 0.00 and 100.00
Primary Key (stdNo, courseID, semesterID)

Here in SQL (I'm using Oracle SQL*plus) .. table STD , SEMESTER works with me, but table COURSE I did not know how to put default value is 2 and table REGISTER dose not work with me at all :(

CREATE TABLE STD 
(
    STDNO CHAR(5) PRIMARY KEY,
    LASTNAME VARCHAR(25) NOT NULL,
    GIVENNAME VARCHAR(50) NOT NULL,
    DEPT CHAR(4)
);

CREATE TABLE SEMESTER 
(
    SEMESTERID CHAR(5) PRIMARY KEY,
    SEMESTERCODE INT CHECK(SEMESTERCODE BETWEEN 1 AND 4),
    YEARS INT CHECK(YEARS BETWEEN 2000 AND 9999)
);

CREATE TABLE COURSE 
(
    COURSEID CHAR(8) PRIMARY KEY,
    COURSETITLE VARCHAR(50) NOT NULL UNIQUE,
    COST DECIMAL(6,2) CHECK(COST >= 0),
    CREDITS INT CHECK(CREDITS BETWEEN 0 AND 200)
);

CREATE TABLE REGISTER 
(
     STDNO CHAR(5)
          FOREIGN KEY REFERENCES STD(STDNO) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     COURSEID CHAR(5) 
          FOREIGN KEY REFERENCES COURSE(COURSEID) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     SEMESTERID CHAR(5)
          FOREIGN KEY REFERENCES SEMESTER(SEMESTERID) 
             ON UPDATE CASCADE ON DELETE CASCADE,
     GRADE CHAR(2),
     MARK DECIMAL(4,2) CHECK(MARK BETWEEN 0.00 AND 100.0),

     PRIMARYKEY(STDNO,COURSEID,SEMESTERID)
);

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s

Charls K V Charls K V · Accepted Answer · 2015-10-24T15:15:41

Please find below the solution.

COURSE - please use the below script to have a default value for CREDITS column

CREATE TABLE COURSE ( COURSEID CHAR(8) PRIMARY KEY, COURSETITLE VARCHAR(50) NOT NULL UNIQUE, COST DECIMAL(6,2) CHECK(COST >= 0), CREDITS INT DEFAULT 2 CHECK(CREDITS BETWEEN 0 AND 200) );
REGISTER - Oracle Does not allow a Foreign Key Constraint with "ON UPDATE CASCADE". So you will have to use triggers for the same. Find below the scripts that you could make use of,

CREATE TABLE REGISTER ( STDNO CHAR(5), COURSEID CHAR(8), -- UPDATED THE DATATYPE TO SAME AS MASTER TABLE SEMESTERID CHAR(5), GRADE CHAR(2), MARK DECIMAL(4,2) CHECK(MARK BETWEEN 0.00 AND 100.0), CONSTRAINT register_pk PRIMARY KEY(STDNO,COURSEID,SEMESTERID), CONSTRAINT register_fk1 FOREIGN KEY (STDNO) REFERENCES STD(STDNO) ON DELETE CASCADE, CONSTRAINT register_fk2 FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID) ON DELETE CASCADE, CONSTRAINT register_fk3 FOREIGN KEY (SEMESTERID) REFERENCES SEMESTER(SEMESTERID) ON DELETE CASCADE );

CREATE OR REPLACE TRIGGER cascade_stdno_update AFTER UPDATE OF stdno ON std FOR EACH ROW BEGIN UPDATE REGISTER SET stdno = :NEW.stdno WHERE stdno = :OLD.stdno; END; /

CREATE OR REPLACE TRIGGER cascade_courseid_update AFTER UPDATE OF courseid ON course FOR EACH ROW BEGIN UPDATE REGISTER SET courseid = :NEW.courseid WHERE courseid = :OLD.courseid; END; /

CREATE OR REPLACE TRIGGER cascade_semesterid_update AFTER UPDATE OF semesterid ON semester FOR EACH ROW BEGIN UPDATE REGISTER SET semesterid = :NEW.semesterid WHERE semesterid = :OLD.semesterid; END; /

on sql Foreign key

1 Answers