Oracle SQL*Plus problem

Beginner problem

Page 1 of 1

1 Replies - 1862 Views - Last Post: 20 November 2009 - 07:48 PM

#1 khimura  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 20-October 09

Oracle SQL*Plus problem

Posted 14 November 2009 - 08:22 PM

hi im doing a project for my class where we first have to drop all of these tables then recreate them and have the constraints inside an alter table syntax. this seems like i did it right but im still getting errors i cant explain
the errors i'm getting are:
SQL> INSERT INTO ENROLLMENT
  2  VALUES (100,1000,'A');
INSERT INTO ENROLLMENT
*
ERROR at line 1:
ORA-02290: check constraint (D03060968.ENROLLMENT_GRADE_CC) violated


SQL> INSERT INTO ENROLLMENT
  2  VALUES (100,1003,'A');
INSERT INTO ENROLLMENT
*
ERROR at line 1:
ORA-02290: check constraint (D03060968.ENROLLMENT_GRADE_CC) violated


SQL> INSERT INTO ENROLLMENT
  2  VALUES (101,1000,'C');
INSERT INTO ENROLLMENT
*
ERROR at line 1:
ORA-02291: integrity constraint (D03060968.ENROLLMENT_CSECID_PK) violated - parent key not found


SQL> INSERT INTO ENROLLMENT
  2  VALUES (102,1000,'C');
INSERT INTO ENROLLMENT
*
ERROR at line 1:
ORA-02291: integrity constraint (D03060968.ENROLLMENT_CSECID_PK) violated - parent key not found

SQL> INSERT INTO COURSE_SECTION
  2  VALUES (1000,1,2,1,12,'MWF',55,100,35);
INSERT INTO COURSE_SECTION
*
ERROR at line 1:
ORA-02291: integrity constraint (D03060968.COURSESECTION_FID_FK) violated - parent key not found

SQL> INSERT INTO TERM
  2  VALUES (1,'Spring 2004','CLOSED');
INSERT INTO TERM
*
ERROR at line 1:
ORA-02290: check constraint (D03060968.TERM_STATUS_CC) violated

SQL> INSERT INTO FACULTY
  2  VALUES(12, 'McClure', 'William', 'L', 55, 7155556407, 'ADJ', 1690);
INSERT INTO FACULTY
*
ERROR at line 1:
ORA-02290: check constraint (D03060968.FACULTY_FRANK_CC) violated


i thought a problem might be not declaring the foreign keys in the ENROLLMENT table for example:
ALTER TABLE ENROLLMENT
ADD CONSTRAINT ENROLLMENT_Sid_FK FOREIGN KEY (Sid) REFERENCES STUDENT (Sid);
ALTER TABLE ENROLLMENT
ADD CONSTRAINT ENROLLMENT_Csecid_FK FOREIGN KEY (Csecid) REFERENCES COURSE_SECTION (Csecid);


but this just causes more errors, please help!!!
heres the entire code:

DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE;

create table LOCATION(
Locid		NUMBER(5) CONSTRAINT LOCATION_Locid_PK PRIMARY KEY,
Bldg_Code	VARCHAR(10) NOT NULL,
Room		 VARCHAR(6) NOT NULL,
Capacity	 NUMBER(5) NOT NULL);

create table FACULTY(
Fid		  NUMBER(4) CONSTRAINT faculty_fid_PK PRIMARY KEY,
Flname	   VARCHAR2(25) NOT NULL,
Ffname	   VARCHAR2(25) NOT NULL,
Fmi		  CHAR(1),
Locid		NUMBER(5) NOT NULL,
Fphone	   NUMBER(10),
F_rank	   VARCHAR2(4),
F_pin		NUMBER(4));

ALTER TABLE FACULTY
ADD CONSTRAINT FACULTY_Locid_FK FOREIGN KEY (Locid) REFERENCES LOCATION (Locid);
ALTER TABLE FACULTY
ADD CONSTRAINT FACULTY_Frank_CC CHECK (F_rank > ANY ('ASSO','FULL','ASST','ADJ'));

create table STUDENT(
Sid		  NUMBER(5) CONSTRAINT STUDENT_Sid_PK PRIMARY KEY,
Slname	   VARCHAR2(25) NOT NULL,
Sfname	   VARCHAR2(25) NOT NULL,
Smi		  CHAR(1),
Saddr		VARCHAR2(30),
Scity		VARCHAR2(30),
Sstate	   CHAR(2),
Szip		 NUMBER(9),
Sphone	   NUMBER(10) NOT NULL,
Sclass	   CHAR(2),
Sdob		 DATE NOT NULL,
S_pin		NUMBER(4) NOT NULL,
Fid		  NUMBER(4));

ALTER TABLE STUDENT
ADD CONSTRAINT STUDENT_Fid_FK FOREIGN KEY (Fid) REFERENCES FACULTY (Fid);
ALTER TABLE STUDENT
ADD CONSTRAINT STUDENT_Sclass_CC CHECK (Sclass > ANY ('FR','SO','JR','SR','GR'));

create table TERM(
Termid	   NUMBER(5) CONSTRAINT TERM_Termid_PK PRIMARY KEY,
Tdesc		VARCHAR2(20) NOT NULL,
Status	   VARCHAR2(20) NOT NULL);

ALTER TABLE TERM
ADD CONSTRAINT TERM_Status_CC CHECK (Status > ANY ('OPEN','CLOSED'));

create table COURSE(
Cid		  NUMBER(6) CONSTRAINT COURSE_Cid_PK PRIMARY KEY,
Callid	   VARCHAR2(10) NOT NULL,
Cname		VARCHAR2(30) NOT NULL,
Ccredit	  NUMBER(2));

create table COURSE_SECTION(
Csecid	   NUMBER(8) CONSTRAINT COURSESECTION_Csecid_PK PRIMARY KEY,
Cid		  NUMBER(6) NOT NULL,
Termid	   NUMBER(5) NOT NULL,
Secnum	   NUMBER(2) NOT NULL,
Fid		  NUMBER(4) NOT NULL,
Day		  VARCHAR2(10),
Locid		NUMBER(5),
Maxenrl	  NUMBER(4) NOT NULL,
Currenrl	 NUMBER(4) NOT NULL);

ALTER TABLE COURSE_SECTION
ADD CONSTRAINT COURSESECTION_Cid_FK FOREIGN KEY (Cid) REFERENCES COURSE (Cid);
ALTER TABLE COURSE_SECTION
ADD CONSTRAINT COURSESECTION_Termid_FK FOREIGN KEY (Termid) REFERENCES TERM (Termid);
ALTER TABLE COURSE_SECTION
ADD CONSTRAINT COURSESECTION_Fid_FK FOREIGN KEY (Fid) REFERENCES FACULTY (Fid);
ALTER TABLE COURSE_SECTION
ADD CONSTRAINT COURSESECTION_Locid_FK FOREIGN KEY (Locid) REFERENCES LOCATION (Locid);


create table ENROLLMENT(
Sid		  NUMBER(5) CONSTRAINT ENROLLMENT_Sid_PK REFERENCES STUDENT,
Csecid	   NUMBER(8) CONSTRAINT ENROLLMENT_Csecid_PK REFERENCES COURSE_SECTION,
Grade		CHAR(1));

ALTER TABLE ENROLLMENT
ADD CONSTRAINT ENROLLMENT_Sid_CsecID_PK PRIMARY KEY (Sid , Csecid);
ALTER TABLE ENROLLMENT
ADD CONSTRAINT ENROLLMENT_Grade_CC CHECK (Grade > ANY ('A','B','C','D','F','I','W'));

INSERT INTO LOCATION
VALUES (53, 'BUS', '424', 45);
INSERT INTO LOCATION
VALUES (54, 'BUS', '402', 35);
INSERT INTO LOCATION
VALUES (55, 'BUS', '433', 100);

INSERT INTO FACULTY
VALUES(10, 'Cox', 'Kim', 'J', 54, 7155551234, 'ASSO', 1181);
INSERT INTO FACULTY
VALUES(11, 'Blanchard', 'Frank', 'R', 54, 7155559087, 'FULL', 1075);
INSERT INTO FACULTY
VALUES(12, 'McClure', 'William', 'L', 55, 7155556407, 'ADJ', 1690);

INSERT INTO STUDENT
VALUES (100,'McClure','Sarah','M','144 Windridge Blvd.','Eau Claire','WI',54703,7155557876,'SR','14-JUL-1979',8891,10);
INSERT INTO STUDENT
VALUES (101,'Bowie','Jim','D','454 St. John Street','Eau Claire','WI',54702,7155552345,'SR','19-AUG-1979',1230,11);
INSERT INTO STUDENT
VALUES (102,'Boone','Daniel',NULL,'8921 Circle Drive','Bloomer','WI',54715,7155553907,'JR','10-OCT-1977',1613,11);

INSERT INTO TERM
VALUES (1,'Spring 2004','CLOSED');
INSERT INTO TERM
VALUES (2,'SUMMER 2004','OPEN');

INSERT INTO COURSE
VALUES (1,'MIS101','Intro. to Info. Systems',3);
INSERT INTO COURSE
VALUES (2,'MIS321','Systems Analysis and Design',3);
INSERT INTO COURSE
VALUES (3,'MIS349','Intro to Database Management',3);

INSERT INTO COURSE_SECTION
VALUES (1000,1,2,1,12,'MWF',55,100,35);
INSERT INTO COURSE_SECTION
VALUES (1001,1,2,2,10,'TTH',54,45,35);
INSERT INTO COURSE_SECTION
VALUES (1002,2,2,3,10,'MWF',53,35,32);
INSERT INTO COURSE_SECTION
VALUES (1003,3,2,1,11,'TTH',54,45,35);

INSERT INTO ENROLLMENT
VALUES (100,1000,'A');
INSERT INTO ENROLLMENT
VALUES (100,1003,'A');
INSERT INTO ENROLLMENT
VALUES (101,1000,'C');
INSERT INTO ENROLLMENT
VALUES (102,1000,'C');
INSERT INTO ENROLLMENT
VALUES (102,1001,NULL);
INSERT INTO ENROLLMENT
VALUES (102,1003,'I');


Is This A Good Question/Topic? 0
  • +

Replies To: Oracle SQL*Plus problem

#2 s-grind  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 86
  • Joined: 20-November 09

Re: Oracle SQL*Plus problem

Posted 20 November 2009 - 07:48 PM

Hi there,

I saw a few 'problems':

* In your constraints: try to replace the '>' with '='.
> means greater than. = means equals.
You have a check constraint, where the value must be in one of those occurences. So '=' solves this problem.

While testing your code in PL SQL Developer, I noticed I was unable to insert student 102 in the database, due to my location: I'm in the Netherlands so 10-OCT-1977 did not work; I had to convert it to '10-OKT-1977'.

Now everything works fine: check your check constraints and replace the '>'!!
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1