|
error - parent key
Using Oracle 8 in WIN98
I am at a loss to what I have done wrong - uld anybody advise me. I have wriiten and created all tables and added values, but the values for ROOMS tabl give me the following error:
INSERT INTO ROOMS VALUES (1001,2001,3001,4001,100.00,'SINGLE')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C00897) violated - parent key not found
Tables and values posed below:
CREATE TABLE ROOMS (
PLACE_NO NUMBER(4) PRIMARY KEY NOT NULL,
ROOM_NO NUMBER(4) NOT NULL,
HALL_NO NUMBER(4) NOT NULL,
NEWS_NO NUMBER(4) NOT NULL,
STUDENT_NO NUMBER(4) NOT NULL,
WEEKLY_RATE NUMBER(6,2),
ROOM_TYPE VARCHAR2(10) CHECK (ROOM_TYPE IN('SINGLE','DOUBLE')),
FOREIGN KEY (HALL_NO) REFERENCES HALLS(HALL_NO),
FOREIGN KEY (NEWS_NO) REFERENCES UNION_NEWS(NEWS_NO),
FOREIGN KEY (STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
INSERT INTO ROOMS VALUES (1001,1000,2001,3001,4001,100.00,'SINGLE');
CREATE TABLE UNION_NEWS (
NEWS_NO NUMBER(4) PRIMARY KEY NOT NULL ,
PLACE_NO NUMBER(4) NOT NULL,
DATE_PUBLISHED DATE DEFAULT SYSDATE);
CREATE TABLE STUDENTS (
STUDENT_NO NUMBER(4) PRIMARY KEY NOT NULL,
FIRSTNAME VARCHAR2(15) ,
LASTNAME VARCHAR2(15),
DATE_OF_BIRTH DATE DEFAULT SYSDATE,
STATUS VARCHAR2(7) CHECK(STATUS IN('PLACED','WAITING')),
STREET VARCHAR2(15) ,
TOWN VARCHAR2(15) ,
POSTCODE CHAR(8));
INSERT INTO STUDENTS VALUES(4001,'ASHISH','RAYAMAJIH','07-MARCH-80','PLACED','25 BAYLIS ROAD','DEPTFORD','SE10 7FR');
INSERT INTO STUDENTS VALUES(4002,'PAUL','LEFEBRE','12-OCT-55','WAITING','98 RIVER ROAD','ERITH','DA8 8RP');
INSERT INTO STUDENTS VALUES(4003,'SARAH','BRYAN','20-JAN-64','PLACED','16 SYLVIA ROAD','STREATHAM','SW16 3GH');
INSERT INTO STUDENTS VALUES(4004,'FASSEL','ABDOU','17-AUG-83','WAITING','11 MALBORO WAY','CHELSEA','W2 5TG');
INSERT INTO STUDENTS VALUES(4005,'NORDINE','SHERIF','09-APR-82','PLACED','78 CAMERON ROAD','NORBURY','SW16 3UT');
INSERT INTO STUDENTS VALUES(4006,'GILBERT','TCHELIBO','09-MAY-67','WAITING','GREEN ROAD','THAMSMEAD','SE28 8FR');
CREATE TABLE LEASES (
LEASE_NO NUMBER(4) PRIMARY KEY NOT NULL ,
STUDENT_NO NUMBER(4) NOT NULL,
FIRSTNAME VARCHAR(15) NOT NULL,
LASTNAME VARCHAR2(15) ,
STARDATE DATE DEFAULT SYSDATE ,
PERIOD NUMBER(2) ,
FINISHDATE DATE DEFAULT SYSDATE,
FOREIGN KEY (STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
INSERT INTO LEASES VALUES(5001,4001,'ASHISH','RAYAMAJIH','19-SEP-03',3,'19-DEC-03');
CREATE TABLE HALLS (
HALL_NO NUMBER(4) PRIMARY KEY NOT NULL,
HALL_NAME VARCHAR2(15) ,
MANAGER_NAME VARCHAR2(15) ,
TELEPHONE NUMBER(15),
STREET VARCHAR2(15) ,
TOWN VARCHAR2(15),
POSTCODE CHAR(8) );
INSERT INTO HALLS VALUES(2001,'KING WILLIAMS','BOB DIXON',02072453419,'23 BEXLEY RD','WOOLWICH','SE24 4RP');
INSERT INTO HALLS VALUES(2002,'QUEEN ANNE','FRED MALO',02082453415,'87 NORTON ROAD','DARTFORD','DA9 6FR');
INSERT INTO HALLS VALUES(2003,'KING HARRIS','GREG FORLAN',02072453888,'BRIGADE RD','WOOLWICH','SE24 4RP');
INSERT INTO HALLS VALUES(2004,'QUEEN ELISABETH','GILBERT TCHECH',01322435678,'59 PAULET RD','CAMBERWELL','SE5 2NF');
INSERT INTO HALLS VALUES(2005,'PRINCE CHARLES','ALAIN BOSIC',02089067682,'90 BELGRAVE RD','BROMLEY','DE4 4RP');
INSERT INTO HALLS VALUES(2006,'KING ALBERT','ADELE MAMOR',02072453333,'12 MANOR WAY','CRYSTAL PALACE','SE29 7RE');
CREATE TABLE STAFF (
STAFF_NO NUMBER(4) PRIMARY KEY NOT NULL,
FIRSTNAME VARCHAR2(15) ,
LASTNAME VARCHAR2(15),
DATE_OF_BIRTH DATE DEFAULT SYSDATE ,
GENDER VARCHAR2(6) ,
POSITION VARCHAR2(10)) ;
INSERT INTO STAFF VALUES(6001,' ALBERT','POKAM','17-APR-66','MALE','MANAGER');
INSERT INTO STAFF VALUES(6002,' ALAIN','NYAMAT','08-NOV-69','MALE','SUPERVISOR');
INSERT INTO STAFF VALUES(6003,' MARIE','BLACK','18-JAN-70','FEMALE','TUTOR');
INSERT INTO STAFF VALUES(6004,' ANNE','TOUMALEU','20-JUL-60','FEMALE','MANAGER');
INSERT INTO STAFF VALUES(6005,' BRICE','NITCHEU','30-JUN-61','MALE','SUPERVISOR');
INSERT INTO STAFF VALUES(6006,' HARAGAN','SCOTT','10-FEB-59','MALE','MANAGER');
CREATE TABLE WORKS_ON (
HALL_NO NUMBER(4) NOT NULL,
STAFF_NO NUMBER(4) NOT NULL,
HOURS NUMBER(4) ,
WORK_DATE DATE DEFAULT SYSDATE,
FOREIGN KEY (HALL_NO) REFERENCES HALLS(HALL_NO),
FOREIGN KEY (STAFF_NO) REFERENCES STAFF(STAFF_NO));
INSERT INTO WORKS_ON VALUES(2001,6002,20,'28-NOV-03');
INSERT INTO WORKS_ON VALUES(2002,6003,30,'30-NOV-03');
INSERT INTO WORKS_ON VALUES(2003,6003,25,'27-NOV-03');
INSERT INTO WORKS_ON VALUES(2004,6004,15,'26-NOV-03');
INSERT INTO WORKS_ON VALUES(2005,6005,23,'25-NOV-03');
INSERT INTO WORKS_ON VALUES(2006,6006,22,'24-NOV-03');
CREATE TABLE DEPENDENTS (
STUDENT_NO NUMBER(4) PRIMARY KEY NOT NULL,
FIRSTNAME VARCHAR2(15) ,
LASTNAME VARCHAR2(15) ,
DATE_OF_BIRTH DATE DEFAULT SYSDATE,
FOREIGN KEY(STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
INSERT INTO DEPENDENTS VALUES(4004,' GEORGE','POKAM','10-APR-95');
CREATE TABLE UNDERGRADUATE(
STUDENT_NO NUMBER(4) PRIMARY KEY NOT NULL,
TUTOR_NAME VARCHAR2(15) ,
STUDENT_LEVEL VARCHAR2(5),
DEPARTMENT VARCHAR2(15),
COURSES VARCHAR2(15),
FOREIGN KEY(STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
INSERT INTO UNDERGRADUATE VALUES(4004,' GEORGE BROWN','1UG','CMS','CISCO');
CREATE TABLE POSTGRADUATE(
STUDENT_NO NUMBER(4) PRIMARY KEY NOT NULL,
SURVISOR_NAME VARCHAR2(15),
STUDENT_LEVEL VARCHAR2(5),
FOREIGN KEY(STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
INSERT INTO POSTGRADUATE VALUES(4004,' ALEX BROWN','MAS');
CREATE TABLE DOUBLE_BED (
PLACE_NO NUMBER(4) PRIMARY KEY NOT NULL,
ROOM_SIZE1 NUMBER(4,2),
NUMBER_OF_BEDS VARCHAR2(8),
FOREIGN KEY(PLACE_NO) REFERENCES ROOMS(PLACE_NO));
CREATE TABLE SINGLE_BED(
PLACE_NO NUMBER(4) PRIMARY KEY NOT NULL,
ROOM_SIZE2 NUMBER(4,2),
NUMBER_OF_BEDS VARCHAR2(8),
FOREIGN KEY(PLACE_NO) REFERENCES ROOMS(PLACE_NO));
CREATE TABLE NEXT_OF_KIN (
STUDENT_NO NUMBER(4) PRIMARY KEY,
FIRSTNAME VARCHAR2(12) ,
LASTNAME VARCHAR2(15),
RELATIONSHIP VARCHAR2(15) ,
TELEPHONE NUMBER(15) ,
STREET VARCHAR2(15) ,
TOWN VARCHAR2(15) ,
POSTCODE CHAR(8),
FOREIGN KEY(STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
EDIT: 6pm I sorted it myself
CREATE TABLE ROOMS (
PLACE_NO NUMBER(4) PRIMARY KEY NOT NULL,
ROOM_NO NUMBER(4) NOT NULL,
HALL_NO NUMBER(4) NOT NULL,
NEWS_NO NUMBER(4) NOT NULL,
STUDENT_NO NUMBER(4) NOT NULL,
WEEKLY_RATE NUMBER(6,2),
ROOM_TYPE VARCHAR2(10) CHECK (ROOM_TYPE IN('SINGLE','DOUBLE')),
FOREIGN KEY (HALL_NO) REFERENCES HALLS(HALL_NO),
FOREIGN KEY (NEWS_NO) REFERENCES UNION_NEWS(NEWS_NO), delete this line
FOREIGN KEY (STUDENT_NO) REFERENCES STUDENTS(STUDENT_NO));
CREATE TABLE UNION_NEWS (
NEWS_NO NUMBER(4) PRIMARY KEY NOT NULL ,
PLACE_NO NUMBER(4) NOT NULL,
DATE_PUBLISHED DATE DEFAULT SYSDATE);
FOREIGN KEY (PLACE_NO) REFERENCES ROOMS(PLACE_NO)); add this line here
Last edited by mbonfyre : November 29th, 2003 at 12:00 PM.
|