ORA-02291: integrity constraint ... violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

Question

I am very new to SQL. This is my first attempt using Live SQL. I keep receiving this error:

ORA-02291: integrity constraint (SQL_ATLZSRILYVHLJWXPIAPEHJEPK.FK_HOSPITAL_SPECIALTY_ID_PATIENT) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

I am able to create my tables and insert information into the HOSPITAL and PATIENT tables. However, I am unable to insert into HOSPITAL_SPECIALTY and SPECIALTY tables. I have tinkered with everything for hours and I don't even know what I am doing incorrectly. HOSPITAL and PATIENT tables were so easy and I was feeling so confident.

    DROP TABLE HOSPITAL;

    CREATE TABLE HOSPITAL( 
        ID INT NOT NULL PRIMARY KEY, 
        NAME VARCHAR(255) NOT NULL, 
        ADDRESS VARCHAR(255) NOT NULL, 
        PHONE INT NOT NULL 
    );

    DROP TABLE PATIENT;

    CREATE TABLE PATIENT( 
        ID INT NOT NULL, 
        NAME VARCHAR(255) NOT NULL, 
        ADDRESS VARCHAR(255) NOT NULL, 
        PHONE INT NOT NULL, 
        EMERGENCY_CONTACT VARCHAR(255) NOT NULL, 
        HOSPITAL_ID INT NOT NULL PRIMARY KEY,
        CONSTRAINT FK_HOSPITAL_ID_HOSPITAL FOREIGN KEY (HOSPITAL_ID) REFERENCES HOSPITAL(ID)
    );

    DROP TABLE HOSPITAL_SPECIALTY;

    CREATE TABLE HOSPITAL_SPECIALTY( 
        HOSPITAL_SPECIALTY_ID INT NOT NULL, 
        HOSPITAL_ID INT NOT NULL, 
        SPECIALTY_ID INT NOT NULL PRIMARY KEY,
        CONSTRAINT FK_HOSPITAL_SPECIALTY_ID_PATIENT FOREIGN KEY (HOSPITAL_SPECIALTY_ID)    REFERENCES PATIENT (HOSPITAL_ID)
    );

    DROP TABLE SPECIALTY;

    CREATE TABLE SPECIALTY(
        SPECIALTY_ID INT NOT NULL,
        SPECIALTY_TYPE INT NOT NULL,
        SPECIALTY_TYPE_NAME VARCHAR(255) NOT NULL,
        CONSTRAINT FK_SPECIALTY_TYPE_HOSPITAL_SPECIALTY FOREIGN KEY (SPECIALTY_TYPE) REFERENCES HOSPITAL_SPECIALTY (SPECIALTY_ID)
    );

    INSERT INTO HOSPITAL VALUES (11, 'Super Good Hospital', '999 Speedy Recover, ATL, GA, 99999', '1232223333');
    INSERT INTO HOSPITAL VALUES (12, 'Piedmont Hospital', '111 Get Better, ATL, GA, 99990', '1232223334');
    INSERT INTO HOSPITAL VALUES (13, 'Northside Hospital', '222 Bum Knee, ATL, GA, 99991', '1232223335');
    INSERT INTO HOSPITAL VALUES (14, 'Emory Hospital', '333 Broken Hip, ATL, GA, 99992', '1232223336');
    INSERT INTO HOSPITAL VALUES (15, 'Northside Hospital', '111 Bum Knee, ATL, GA, 99991', '1232223334');

    SELECT * FROM HOSPITAL

    INSERT INTO PATIENT VALUES (37442, 'Billy Jeans', '1111 Somewhere Here, ATL, GA, 11111', '4048888888', 'Jimmy John', '15');
    INSERT INTO PATIENT VALUES (37443, 'Allie Grater', '2222 Somewhere There, ATL, GA, 11112', '4048888889', 'Jimmy John', '11');
    INSERT INTO PATIENT VALUES (37444, 'Peg Legge', '3333 Somewhere Where, ATL, GA, 11113', '4048888880', 'Jimmy John', '13');
    INSERT INTO PATIENT VALUES (37445, 'Olive Yew', '4444 Somewhere Overthere, ATL, GA, 11114', '4048888881', 'Jimmy John', '12');
    INSERT INTO PATIENT VALUES (37446, 'Anne T Dote', '5555 Somewhere Overhere, ATL, GA, 11115', '4048888882', 'Jimmy John', '14');

    SELECT * FROM PATIENT

    INSERT INTO HOSPITAL_SPECIALTY VALUES (111, 11, 1);
    INSERT INTO HOSPITAL_SPECIALTY VALUES (112, 11, 1);
    INSERT INTO HOSPITAL_SPECIALTY VALUES (113, 11, 1);
    INSERT INTO HOSPITAL_SPECIALTY VALUES (114, 11, 1);
    INSERT INTO HOSPITAL_SPECIALTY VALUES (115, 11, 1);

    SELECT * FROM HOSPITAL_SPECIALTY

    INSERT INTO SPECIALTY VALUES (111, 33, 'Brain');
    INSERT INTO SPECIALTY VALUES (112, 44, 'General');
    INSERT INTO SPECIALTY VALUES (113, 55, 'Surgery');
    INSERT INTO SPECIALTY VALUES (114, 66, 'Covid');
    INSERT INTO SPECIALTY VALUES (115, 77, 'Bodily Injured');

    SELECT * FROM SPECIALTY

Answer 1

It looks like you have your FK dependencies mixed up a little. It looks like a cart-before-the-horse design ...which shouldn't give this error ...unless you aren't showing all of the data that you are trying to insert.

Your table HOSPITAL_SPECIALTY looks like it is supposed to be an "intersection table", which connects/references HOSPITAL with SPECIALTY. Your table has columns to match HOSPITAL(HOSPITAL_ID) and SPECIALTY(SPECIALTY_ID) but instead of making FKs for those, you seem to be defining a single FK which enforces a reference to the column in PATIENT (HOSPITAL_ID).

Again, if you are only trying to load the data shown above, I don't think you will get the error you've shown, but if you try to load more data (or UPDATE, or DELETE someday) you will run into problems.

I would recommend a design, and load plan more like this one:

--It is best to drop all tables before creating.   
--You have to drop in reverse-order of your dependencies
DROP TABLE HOSPITAL_SPECIALTY; --depends on patient & hospital
DROP TABLE PATIENT;            --depends on hospital
DROP TABLE HOSPITAL;
DROP TABLE SPECIALTY;

--Now create your base tables
CREATE TABLE HOSPITAL( 
    ID INT NOT NULL PRIMARY KEY, 
    NAME VARCHAR(255) NOT NULL, 
    ADDRESS VARCHAR(255) NOT NULL, 
    PHONE INT NOT NULL 
);

CREATE TABLE SPECIALTY(
    SPECIALTY_ID INT NOT NULL,
    SPECIALTY_TYPE INT NOT NULL,
    SPECIALTY_TYPE_NAME VARCHAR(255) NOT NULL
    --This looks backwards. I think this constraint would be better on HOSPITAL_SPECIALTY
    --, CONSTRAINT FK_SPECIALTY_TYPE_HOSPITAL_SPECIALTY FOREIGN KEY (SPECIALTY_TYPE) REFERENCES HOSPITAL_SPECIALTY (SPECIALTY_ID)
);

--PATIENT references HOSPITAL
CREATE TABLE PATIENT( 
    ID INT NOT NULL, 
    NAME VARCHAR(255) NOT NULL, 
    ADDRESS VARCHAR(255) NOT NULL, 
    PHONE INT NOT NULL, 
    EMERGENCY_CONTACT VARCHAR(255) NOT NULL, 
    HOSPITAL_ID INT NOT NULL PRIMARY KEY,
    CONSTRAINT FK_HOSPITAL_ID_HOSPITAL FOREIGN KEY (HOSPITAL_ID) REFERENCES HOSPITAL(ID)
);


-- Since this table seems to depend on two other tables, it is best to add it last
CREATE TABLE HOSPITAL_SPECIALTY( 
    HOSPITAL_SPECIALTY_ID INT NOT NULL, 
    HOSPITAL_ID INT NOT NULL, 
    SPECIALTY_ID INT NOT NULL PRIMARY KEY,
    --since HOSPITAL_ID originates in the HOSPITAL table, I would recommend pointing your FK at HOSPITAL instead of PATIENT
    --CONSTRAINT FK_HOSPITAL_SPECIALTY_ID_PATIENT FOREIGN KEY (HOSPITAL_SPECIALTY_ID)    REFERENCES PATIENT (HOSPITAL_ID)
    CONSTRAINT FK_HOSPITAL_SPECIALTY_HOSPITAL_ID FOREIGN KEY (HOSPITAL_ID)    REFERENCES HOSPITAL (HOSPITAL_ID),
    --Also, instead of the other FK pointing from SPECIALTY to here, it should point the other direction (from here to SPECIALTY) 
    CONSTRAINT FK_HOSPITAL_SPECIALTY_SPECIALTY_ID FOREIGN KEY (SPECIALTY_ID) REFERENCES SPECIALTY (SPECIALTY_ID)

);


--now load data in the same order: base tables, then dependent
INSERT INTO HOSPITAL VALUES (11, 'Super Good Hospital', '999 Speedy Recover, ATL, GA, 99999', '1232223333');
INSERT INTO HOSPITAL VALUES (12, 'Piedmont Hospital', '111 Get Better, ATL, GA, 99990', '1232223334');
INSERT INTO HOSPITAL VALUES (13, 'Northside Hospital', '222 Bum Knee, ATL, GA, 99991', '1232223335');
INSERT INTO HOSPITAL VALUES (14, 'Emory Hospital', '333 Broken Hip, ATL, GA, 99992', '1232223336');
INSERT INTO HOSPITAL VALUES (15, 'Northside Hospital', '111 Bum Knee, ATL, GA, 99991', '1232223334');

SELECT * FROM HOSPITAL

INSERT INTO SPECIALTY VALUES (111, 33, 'Brain');
INSERT INTO SPECIALTY VALUES (112, 44, 'General');
INSERT INTO SPECIALTY VALUES (113, 55, 'Surgery');
INSERT INTO SPECIALTY VALUES (114, 66, 'Covid');
INSERT INTO SPECIALTY VALUES (115, 77, 'Bodily Injured');

SELECT * FROM SPECIALTY

INSERT INTO PATIENT VALUES (37442, 'Billy Jeans', '1111 Somewhere Here, ATL, GA, 11111', '4048888888', 'Jimmy John', '15');
INSERT INTO PATIENT VALUES (37443, 'Allie Grater', '2222 Somewhere There, ATL, GA, 11112', '4048888889', 'Jimmy John', '11');
INSERT INTO PATIENT VALUES (37444, 'Peg Legge', '3333 Somewhere Where, ATL, GA, 11113', '4048888880', 'Jimmy John', '13');
INSERT INTO PATIENT VALUES (37445, 'Olive Yew', '4444 Somewhere Overthere, ATL, GA, 11114', '4048888881', 'Jimmy John', '12');
INSERT INTO PATIENT VALUES (37446, 'Anne T Dote', '5555 Somewhere Overhere, ATL, GA, 11115', '4048888882', 'Jimmy John', '14');

SELECT * FROM PATIENT

INSERT INTO HOSPITAL_SPECIALTY VALUES (111, 11, 1);
INSERT INTO HOSPITAL_SPECIALTY VALUES (112, 11, 1);
INSERT INTO HOSPITAL_SPECIALTY VALUES (113, 11, 1);
INSERT INTO HOSPITAL_SPECIALTY VALUES (114, 11, 1);
INSERT INTO HOSPITAL_SPECIALTY VALUES (115, 11, 1);

SELECT * FROM HOSPITAL_SPECIALTY

Answer 2

I can see a number of issue with your design:

1. PATIENT table: PK should be the ID, not HOSPITAL_ID
2. You should create table SPECIALTY before table HOSPITAL_SPECIALTY
3. HOSPITAL_SPECIALTY table:
   • PK should be HOSPITAL_SPECIALTY_ID
   • HOSPITAL_ID + SPECIALTY_ID should be defined as unique
   • HOSPITAL_ID should be an FK to HOSPITAL.ID
   • SPECIALTY_ID should be an FK to SPECIALTY.ID

You then need to insert data in the order

1. Hospital
2. Patient
3. Speciality
4. Hospital-Speciality