SQL Error 'Parent Key Not Found' for Improperly Used Foreign Keys?

1
votes

I updated my attempt, keeping some things as CHAR's and still get a similar error:ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (MMM1339.ITEMNO_PHAR_FK) violated - parent key not found

Requisition's foreign keys all have values in the different supply primary keys, so I'm not sure why there is still an issue. 

CREATE TABLE SUPPLIER
(SUPPLIERNO   CHAR(6),
 SUPPLIERNAME VARCHAR2(100),
 PHONENO VARCHAR2(12),
 ADDRESS VARCHAR(100),
 FAXNO VARCHAR(12),
 CONSTRAINT SUPPLIERNO_SSPL_PK PRIMARY KEY(SUPPLIERNO));

CREATE TABLE SUPPLIES_PHARMACEUTICAL
(ITEMNO         CHAR(6),
SUPPLIERNO      CHAR(6),
NAME            VARCHAR2(25),
DESCRIPTION     VARCHAR2(25),
QUANTITYINSTOCK INT,
REORDERLEVEL    INT,
COSTPERUNIT     DECIMAL(6,2),
DOSAGE          VARCHAR2(12),
CONSTRAINT ITEMNO_PHAR_PK PRIMARY KEY(ITEMNO));

CREATE TABLE SUPPLIES_SURGICAL
(ITEMNO         CHAR(6),
NAME            VARCHAR2(25),
DESCRIPTION     VARCHAR2(25),
QUANTITYINSTOCK INT,
REORDERLEVEL    INT,
COSTPERUNIT     DECIMAL(6,2),
SUPPLIERNO      CHAR(6),
CONSTRAINT ITEMNO_SUP_PK PRIMARY KEY(ITEMNO));

CREATE TABLE SUPPLIES_NONSURGICAL
(ITEMNO         CHAR(6),
NAME            VARCHAR2(25),
DESCRIPTION     VARCHAR2(25),  
QUANTITYINSTOCK INT,
REORDERLEVEL    INT,
COSTPERUNIT     DECIMAL(6,2),
SUPPLIERNO      CHAR(6),
CONSTRAINT ITEMNO_NONSURG_PK PRIMARY KEY(ITEMNO));

CREATE TABLE STAFF_CHARGENURSE
(STAFFNO   CHAR(6),
 ADDRESS   VARCHAR2(25),
 POSITION  VARCHAR2(12),
 BUDGET    DECIMAL(6,2),
 SPECIALTY VARCHAR2(12),
 CONSTRAINT STAFFNO_CHNURSE_PK PRIMARY KEY(STAFFNO));

 CREATE TABLE REQUISITION
 (REQNO CHAR(6),
  STAFFNO CHAR(6),
  STAFFNAME VARCHAR2(25),
  WARDNO CHAR(6),
  ITEMNO CHAR(6),
  QUANTITY INT,
  DATEORDERED DATE,
  DATERECIEVED DATE,
  CONSTRAINT REQ_PK PRIMARY KEY(REQNO));

 ALTER TABLE SUPPLIES_PHARMACEUTICAL ADD CONSTRAINT SUPPLIERNO_PHA_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE SUPPLIES_SURGICAL ADD CONSTRAINT SUPPLIERNO_SURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE SUPPLIES_NONSURGICAL ADD CONSTRAINT SUPPLIERNO_NONSURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE REQUISITION ADD CONSTRAINT STAFFNO_REQ_FK FOREIGN KEY(STAFFNO) REFERENCES STAFF_CHARGENURSE(STAFFNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_PHAR_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SURG_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_SURGICAL(ITEMNO) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_NONSURG_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_NONSURGICAL(ITEMNO) DEFERRABLE INITIALLY DEFERRED;

 INSERT INTO REQUISITION VALUES('000001', '345000', 'Julie Wood', '8', '888520', 2, '27-FEB-2018', '15-MAR-2018');
 INSERT INTO REQUISITION VALUES('000002', '345000', 'Julie Wood', '8', '923956', 1, '25-FEB-2018', '28-FEB-2018');
 INSERT INTO REQUISITION VALUES('000003', '345000', 'Julie Wood', '8', '054802', 3, '20-FEB-2018', '22-FEB-2018');

 INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('823456', '100001', 'Zanax', 'Anti Depressant', 8, 2, 100.50, '50mg');
 INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('923956', '100001', 'Zupridol', 'Blood Pressure Treatment', 12, 5, 50, '20mg');
 INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('003952', '200001', 'Amibreezax', 'Antifungal Ear Wax', 2, 1, 200, '5g');
 INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES ('004955', '200001', 'Ambridax', 'Blood Fungus Treatment', 5, 10, 20, '2mg');

 INSERT INTO SUPPLIES_SURGICAL VALUES ('054802', 'Scalpel', 'Scalping Tool', 20, 10, 200.42, '100001');
 INSERT INTO SUPPLIES_SURGICAL VALUES ('634520', 'Stitches', 'Suture Tool', 100, 10, 2.50, '200001');

 INSERT INTO SUPPLIES_NONSURGICAL VALUES ('888520', 'Cart', '5ftx2ftx3ft', 2, 0, 200.00, '100001');
 INSERT INTO SUPPLIES_NONSURGICAL VALUES ('000423', 'Tool Holder', 'Holds Inspection Equip.', 4, 2, 50.00, '100001');

 INSERT INTO STAFF_CHARGENURSE VALUES('345000', '32 Stark St. Portland, OR', 'Charge Nurse', 8000.99, 'Head Trauma');
 INSERT INTO STAFF_CHARGENURSE VALUES('246000', '18 Wilson Rd Portland, OR', 'Charge Nurse', 6000, 'Epidermus');

 INSERT INTO SUPPLIER VALUES ('100001','Company A', '503-222-3333', '100 SE Stark Rd Portland, OR', '503-666-4444');
 INSERT INTO SUPPLIER VALUES ('200001','Company B', '666-333-4444', '500 SE Bilerica Rd Akron, OH', '666-444-3333');

 COMMIT;
4
sqloracleforeign-keys
I've never heard of a MANDATORY, OR but it looks like your three SUPPLIES tables should be a single table with a classification column. I'm a bit puzzled by the classifications, though - pharmaceutical, surgical, and non-surgical. Shouldn't surgical and non-surgical cover everything? By the way, all of those CHR columns should be VARCHAR2. - William Robertson

4 Answers

1
votes

Unfortunately (for your DDL code) I have to agree with @William Robertson - you need to change your model, and thus, you need to rework your DDL code completely. Reasons for this being as follows:

Looking at a reverse-engineered model, from your original DDL code, we can see that REQUISITION has 3 (sorry, 4) parent tables. That's why its inserts always fail, due to foreign key violations. Your model:

enter image description here

A simplified example, illustrating the problem in form of DDL code, could look something like this:

create table parent1 ( id number primary key ) ; -- analogy: supplies_pharmaceutical
create table parent2 ( id number primary key ) ; -- analogy: supplies_nonsurgical
create table parent3 ( id number primary key ) ; -- analogy: supplies_surgical

create table child ( -- analogy: requisitions
  id number primary key
, parentid number 
);

alter table child add constraint fkey_parent1
foreign key ( parentid ) references parent1 ( id ) ;

alter table child add constraint fkey_parent2
foreign key ( parentid ) references parent2 ( id ) ;

alter table child add constraint fkey_parent3
foreign key ( parentid ) references parent3 ( id ) ;

begin
  insert into parent1 ( id ) values ( 1 ) ;
  insert into parent2 ( id ) values ( 2 ) ;
  insert into parent3 ( id ) values ( 3 ) ;
end ;
/

So, with our parent tables populated, just a quick check:

select 'parent1 (id) -> ' || id from parent1
union all
select 'parent2 (id) -> ' ||  id from parent2
union all
select 'parent3 (id) -> ' ||  id from parent3
;

-- result
'PARENT1(ID)->'||ID  
parent1 (id) -> 1    
parent2 (id) -> 2    
parent3 (id) -> 3

All good. Now we want to insert some rows into our child table.

insert into child ( id, parentid ) values ( 100, 1 ) ;
-- ORA-02291: integrity constraint (...FKEY_PARENT3) violated - parent key not found

insert into child ( id, parentid ) values ( 101, 2 ) ;
-- ORA-02291: integrity constraint (...FKEY_PARENT3) violated - parent key not found

insert into child ( id, parentid ) values ( 102, 3 ) ;
-- ORA-02291: integrity constraint (...FKEY_PARENT2) violated - parent key not found

You see that the correct parent table does not just "get picked automatically".

In William's model OTOH, REQUISITION has only one parent (table) with respect to "supplies". Which should make inserting rows much easier ... see below.

enter image description here

1
votes

Edited:

Combine SUPPLIES_SURGICAL and SUPPLIES_NONSURGICAL as a single SUPPLIES table, and define PHARMA_DOSAGE as a child table under that.

Now SUPPLIES rows must be either surgical or not, which can be implemented as a Y/N flag and enforced by a check constraint. (One possible problem with this, though, is that the model does not prevent you from adding a dosage for a surgical item.)

In the following, I've made ITEMNO an integer and fixed your CHAR columns. You might consider reviewing the datatype of all columns ending in NO, especially if they are going to be generated sequentially. I would also make all generated PK columns identity columns, but I'll leave the details up to you.

create table supplier
( supplierno      integer primary key );

create table supplies
( itemno          integer
                  constraint supplies_pk primary key
, supplierno      constraint supplies_supplier_fk references supplier(supplierno)
, name            varchar2(25) not null
, description     varchar2(25) null
, quantityinstock integer
, reorderlevel    integer
, costperunit     number(6,2)
, is_surgical     varchar2(1) not null
                  constraint supplies_surgical_yn_chk check(is_surgical in ('Y','N')) );

create table pharma_dosage
( itemno          constraint pharma_supplies_fk references supplies(itemno)
                  constraint pharma_supplies_pk primary key
, dosage          varchar2(12) not null );

create table staff_chargenurse
( staffno         varchar2(6)
                  constraint staffno_chnurse_pk primary key
, address         varchar2(25)
, position        varchar2(12)
, budget          number(6,2)
, specialty       varchar2(12) );

create table requisition
( reqno           varchar2(6)
                  constraint reqno_pk primary key
, staffno         constraint staffno_req_fk references staff_chargenurse(staffno)
, staffname       varchar2(25)
, wardno          varchar2(6)
, itemno          constraint itemno_supplies_fk references supplies(itemno)
, quantity        integer
, dateordered     date
, daterecieved    date );

insert into supplier values ('100001');
insert into supplier values ('200001');

insert into supplies (itemno, supplierno, name, description, quantityinstock, reorderlevel, costperunit, is_surgical) values (823456, '100001', 'Zanax', 'Anti Depressant', 8, 2, 100.50, 'N');
insert into supplies (itemno, supplierno, name, description, quantityinstock, reorderlevel, costperunit, is_surgical) values (923956, '100001', 'Zupridol', 'Blood Pressure Treatment', 12, 5, 50, 'N');
insert into supplies (itemno, supplierno, name, description, quantityinstock, reorderlevel, costperunit, is_surgical) values (003952, '200001', 'Amibreezax', 'Antifungal Ear Wax', 2, 1, 200, 'N');
insert into supplies (itemno, supplierno, name, description, quantityinstock, reorderlevel, costperunit, is_surgical) values (004955, '200001', 'Ambridax', 'Blood Fungus Treatment', 5, 10, 20, 'N');

insert into pharma_dosage (itemno, dosage) values (823456, '50mg');
insert into pharma_dosage (itemno, dosage) values (923956, '20mg');
insert into pharma_dosage (itemno, dosage) values (003952, '5g');
insert into pharma_dosage (itemno, dosage) values (004955, '2mg');

insert into supplies (itemno, name, description, quantityinstock, reorderlevel, costperunit, supplierno, is_surgical) values (054802, 'Scalpel', 'Scalping Tool', 20, 10, 200.42, '100001', 'Y');
insert into supplies (itemno, name, description, quantityinstock, reorderlevel, costperunit, supplierno, is_surgical) values (634520, 'Stitches', 'Suture Tool', 100, 10, 2.50, '200001', 'Y');

insert into supplies (itemno, name, description, quantityinstock, reorderlevel, costperunit, supplierno, is_surgical) values (888520, 'Cart', '5ftx2ftx3ft', 2, 0, 200.00, '100001', 'N');
insert into supplies (itemno, name, description, quantityinstock, reorderlevel, costperunit, supplierno, is_surgical) values (000423, 'Tool Holder', 'Holds Inspection Equip.', 4, 2, 50.00, '100001', 'N');

insert into staff_chargenurse values('345000', '32 Stark St. Portland, OR', 'Charge Nurse', 8000.99, 'Head Trauma');
insert into staff_chargenurse values('246000', '18 Wilson Rd Portland, OR', 'Charge Nurse', 6000, 'Epidermus');

insert into requisition (reqno, staffno, staffname, wardno, itemno, quantity, dateordered, daterecieved) values('000001', '345000', 'Julie Wood', '8', 888520, 2, date '2018-02-27', date '2018-03-15');
insert into requisition (reqno, staffno, staffname, wardno, itemno, quantity, dateordered, daterecieved) values('000002', '345000', 'Julie Wood', '8', 823456, 1, date '2018-02-25', date '2018-02-28');
insert into requisition (reqno, staffno, staffname, wardno, itemno, quantity, dateordered, daterecieved) values('000003', '345000', 'Julie Wood', '8', 054802, 3, date '2018-02-20', date '2018-02-22');
0
votes

we need some rearrangements to suppress ORA-02291 error :

  • First of all, the INSERT statements should be ended with semi-colons.
  • There should exist a table called SUPPLIER to be referenced.

  • Create tables without FOREIGN KEYS, and after all of them created, ALTER tables to include FOREIGN KEYS. To Suppress data-inconsistency during INSERT operations, we need to create FOREIGN KEYS as DEFERRABLE INITIALLY DEFERRED(with the following way,we won't encounter any error even if when the ending COMMIT issued ).

    I think, you'd better storing SUPPLIERNO columns as INT(EGER) or NUMBER, instead of CHAR. Besides, CHAR data type is deprecated, it's better to convert them to VARCHAR2.

So, use the following series of action ( At the bottom part, I've changed some values of REQUISITION not to violate Foreign Constraints ): 

    CREATE TABLE SUPPLIER
    (SUPPLIERNO   INT,
     SUPPLIERNAME VARCHAR2(100),
     CONSTRAINT SUPPLIERNO_SSPL_PK PRIMARY KEY(SUPPLIERNO));

    CREATE TABLE SUPPLIES_PHARMACEUTICAL
    (ITEMNO         INT,
    SUPPLIERNO      INT,
    NAME            VARCHAR2(25),
    DESCRIPTION     VARCHAR2(25),
    QUANTITYINSTOCK INT,
    REORDERLEVEL    INT,
    COSTPERUNIT     DECIMAL(6,2),
    DOSAGE          VARCHAR2(12),
    CONSTRAINT ITEMNO_PHAR_PK PRIMARY KEY(ITEMNO));

    CREATE TABLE SUPPLIES_SURGICAL
    (ITEMNO         INT,
    NAME            VARCHAR2(25),
    DESCRIPTION     VARCHAR2(25),
    QUANTITYINSTOCK INT,
    REORDERLEVEL    INT,
    COSTPERUNIT     DECIMAL(6,2),
    SUPPLIERNO      INT,
    CONSTRAINT ITEMNO_SUP_PK PRIMARY KEY(ITEMNO));

    CREATE TABLE SUPPLIES_NONSURGICAL
    (ITEMNO         INT,
    NAME            VARCHAR2(25),
    DESCRIPTION     VARCHAR2(25),  
    QUANTITYINSTOCK INT,
    REORDERLEVEL    INT,
    COSTPERUNIT     DECIMAL(6,2),
    SUPPLIERNO      INT,
    CONSTRAINT ITEMNO_NONSURG_PK PRIMARY KEY(ITEMNO));

    CREATE TABLE STAFF_CHARGENURSE
    (STAFFNO   INT,
     ADDRESS   VARCHAR2(25),
     POSITION  VARCHAR2(12),
     BUDGET    DECIMAL(6,2),
     SPECIALTY VARCHAR2(12),
     CONSTRAINT STAFFNO_CHNURSE_PK PRIMARY KEY(STAFFNO));

     ALTER TABLE SUPPLIES_PHARMACEUTICAL ADD CONSTRAINT SUPPLIERNO_PHA_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;
     ALTER TABLE SUPPLIES_SURGICAL ADD CONSTRAINT SUPPLIERNO_SURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;
     ALTER TABLE SUPPLIES_NONSURGICAL ADD CONSTRAINT SUPPLIERNO_NONSURG_FK FOREIGN KEY(SUPPLIERNO) REFERENCES SUPPLIER(SUPPLIERNO) DEFERRABLE INITIALLY DEFERRED;

     INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES (888520, 100001, 'Zanax', 'Anti Depressant', 8, 2, 100.50, '50mg');
     INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES (923956, 100001, 'Zupridol', 'Blood Pressure Treatment', 12, 5, 50, '20mg');
     INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES (634520, 200001, 'Amibreezax', 'Antifungal Ear Wax', 2, 1, 200, '5g');
     INSERT INTO SUPPLIES_PHARMACEUTICAL VALUES (4955, 200001, 'Ambridax', 'Blood Fungus Treatment', 5, 10, 20, '2mg');

     INSERT INTO SUPPLIES_SURGICAL VALUES (888520, 'Scalpel', 'Scalping Tool', 20, 10, 200.42, 100001);
     INSERT INTO SUPPLIES_SURGICAL VALUES (634520, 'Stitches', 'Suture Tool', 100, 10, 2.50, 200001);

     INSERT INTO SUPPLIES_NONSURGICAL VALUES (888520, 'Cart', '5ftx2ftx3ft', 2, 0, 200.00, 100001);
     INSERT INTO SUPPLIES_NONSURGICAL VALUES (634520, 'Tool Holder', 'Holds Inspection Equip.', 4, 2, 50.00, 100001);


     INSERT INTO STAFF_CHARGENURSE VALUES(345000, '32 Stark St. Portland, OR', 'Charge Nurse', 8000.99, 'Head Trauma');
     INSERT INTO STAFF_CHARGENURSE VALUES(246000, '18 Wilson Rd Portland, OR', 'Charge Nurse', 6000, 'Epidermus');

     INSERT INTO SUPPLIER VALUES (100001,'Company A');
     INSERT INTO SUPPLIER VALUES (200001,'Company B');

     CREATE TABLE REQUISITION
     (REQNO       INT,
     STAFFNO      INT,
     STAFFNAME    VARCHAR2(25),
     WARDNO       INT,
     ITEMNO       INT,
     QUANTITY     INT,
     DATEORDERED  DATE,
     DATERECIEVED DATE,
     CONSTRAINT REQNO_PK PRIMARY KEY(REQNO),
     CONSTRAINT STAFFNO_REQ_FK FOREIGN KEY(STAFFNO) REFERENCES STAFF_CHARGENURSE(STAFFNO),
     CONSTRAINT ITEMNO_PHAR_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO),
     CONSTRAINT ITEMNO_SURG_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_SURGICAL(ITEMNO),
     CONSTRAINT ITEMNO_NONSURG_FK FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_NONSURGICAL(ITEMNO));
     INSERT INTO REQUISITION VALUES(1, 345000, 'Julie Wood', 8, 888520, 2, '27-FEB-2018', '15-MAR-2018');
     INSERT INTO REQUISITION VALUES(2, 345000, 'Julie Wood', 8, 634520, 1, '25-FEB-2018', '28-FEB-2018');
     INSERT INTO REQUISITION VALUES(3, 345000, 'Julie Wood', 8, 634520, 3, '20-FEB-2018', '22-FEB-2018');

     COMMIT;
0
votes

This answer relates to your question about why your updated definition with deferred constraints still gives an FK violation.

After inserting the rows but before attempting to commit:

select r.reqno, r.itemno
     , sp.name as pharmaceutical
     , ss.name as surgical
     , sn.name as nonsurgical
from   requisition r
       left join supplies_pharmaceutical sp on sp.itemno = r.itemno
       left join supplies_surgical ss on ss.itemno = r.itemno
       left join supplies_nonsurgical sn on sn.itemno = r.itemno;

REQNO   ITEMNO  PHARMACEUTICAL  SURGICAL        NONSURGICAL
------- ------- --------------- --------------- ---------------
000001  888520                                  Cart
000002  923956  Zupridol                        
000003  054802                  Scalpel

Item 888520 violates constraint ITEMNO_PHAR_FK because it is not defined in parent table SUPPLIES_PHARMACEUTICAL:

ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_PHAR_FK
    FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO) ...

It also violates ITEMNO_SURG_FK because 888520 is not in parent table SUPPLIES_SURGICAL:

ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SURG_FK
    FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_SURGICAL(ITEMNO) ...

And so on. Each requisition row violates two constraints. Defining the constraints as deferred doesn't change anything except the timing of the error.

To satisfy your FK definitions, every itemno would need to exist in all three supplies tables.

If you really need to have three supplies tables, you will need three separate supply item columns, each with its own FK to the corresponding table.

(And you really should change those CHAR columns to the standard string type. CHAR has no place in an Oracle database, it's purely for weird portability and ANSI completeness requirements, not a type you are actually supposed to use.)