如何修复“违反完整性约束 (PROD_SUP_FK) - 未找到父键”？

Question

我这样做了

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');

，这就是结果

ORA-02291: 违反完整性约束 (PROD_SUP_FK) - 未找到父键

ORA-02291: 违反完整性约束 (PROD_SUP_FK) - 未找到父键

ORA-06512：位于“SYS.DBMS_SQL”，第 1721 行

插入产品（prod_id、prod_price、prod_avail、prod_mdate、prod_edate、cust_id、sto_id、sup_id） 值('RBL01001',4.50,'可用',TO_DATE('29-MAR-21','DD-MON-YY'),TO_DATE('29-APR-21','DD-MON-YY'), 0001,'Kapar1','SUPP-001');

表产品

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
 );

表供应商

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 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);

使用 Oracle APEX 21.2.0

Answer 1

首先，您必须将行插入“主”表（customer、supplier、bakery/store），然后进入他们的“子”表（产品）。

---

顺便问一下，这个面包店/商店是做什么的？这样的语法是无效的：

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