How can I fix 'integrity constraint (PROD_SUP_FK) violated

I did this

INSERT INTO product (prod_id, prod_price, prod_avail, prod_mdate, prod_edate, cust_id, sto_id, sup_id)
Values('RBL01001',4.50,'Available',TO_DATE('29-MAR-21','DD-MON-YY'),TO_DATE('29-APR-21','DD-MON-YY'),0001,'Kapar1','SUPP-001');

and this is the result

ORA-02291: integrity constraint (PROD_SUP_FK) violated - parent key not found

ORA-06512: at "SYS.DBMS_SQL", line 1721

INSERT INTO product (prod_id, prod_price, prod_avail, prod_mdate, prod_edate, cust_id, sto_id, sup_id) Values('RBL01001',4.50,'Available',TO_DATE('29-MAR-21','DD-MON-YY'),TO_DATE('29-APR-21','DD-MON-YY'),0001,'Kapar1','SUPP-001');

Table product

CREATE TABLE product
 (
   prod_id      VARCHAR2 (10) CONSTRAINT prod_id_nn NOT NULL ENABLE,
   prod_price   NUMBER (3, 2) CONSTRAINT prod_price_nn NOT NULL ENABLE,
   prod_avail   VARCHAR2 (20) CONSTRAINT prod_avail_nn NOT NULL ENABLE,
   prod_mdate   DATE CONSTRAINT prod_mdate_nn NOT NULL ENABLE,
   prod_edate   DATE CONSTRAINT prod_edate_nn NOT NULL ENABLE,
   cust_id      NUMBER (4, 0),
   sto_id       VARCHAR (8) CONSTRAINT sto_id_nn NOT NULL ENABLE,
   sup_id       VARCHAR (10),
   CONSTRAINT prod_id_pk PRIMARY KEY (prod_id) USING INDEX ENABLE,
   CONSTRAINT prod_date_interval CHECK (prod_edate > prod_mdate) ENABLE
 );

table supplier

CREATE TABLE  supplier
(
  sup_id VARCHAR2(10) CONSTRAINT sup_id_nn NOT NULL ENABLE,
  sup_name VARCHAR2(500) CONSTRAINT sup_name_NN NOT NULL ENABLE,
  sup_add VARCHAR2(500) CONSTRAINT sup_add_nn NOT NULL ENABLE,
  CONSTRAINT sup_id_pk PRIMARY KEY (sup_id) USING INDEX  ENABLE
);

Alter table product

ALTER TABLE  product ADD CONSTRAINT prod_cust_fk FOREIGN KEY (cust_id)
        REFERENCES  customer (cust_id) ENABLE;
ALTER TABLE  product ADD CONSTRAINT prod_sto_fk FOREIGN KEY (sto_id)
        REFERENCES  bakery / store (sto_id) ENABLE;
ALTER TABLE  product ADD CONSTRAINT prod_sup_fk FOREIGN KEY (sup_id)
        REFERENCES  supplier (sup_id) ENABLE;

CREATE INDEX  prod_cust_ix ON  product (cust_id);
CREATE INDEX  prod_sto_ix ON  product (sto_id);
CREATE INDEX  prod_sup_ix ON  product (sup_id);

using Oracle APEX 21.2.0

CodePudding user response：

First you have to insert rows into "master" tables (customer, supplier, bakery/store), then into their "child" table (product).

By the way, what is this bakery / store thing supposed to do? Such a syntax is invalid:

ALTER TABLE  product ADD CONSTRAINT prod_sto_fk FOREIGN KEY (sto_id)
    REFERENCES  bakery / store (sto_id) ENABLE;
                --------------
                this