#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    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 01:00 PM.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    You have FK (foreign key) constraints, and the data you are inserting does not match those constraints.

IMN logo majestic logo threadwatch logo seochat tools logo