sql - 错误报告 - ORA-02291 : integrity constraint violated - parent key not found

Question

所以我在 Oracle 中编写了一些代码并建立了下表:

CREATE TABLE users
(
    user_id         NUMBER          NOT NULL,
    email_address   VARCHAR2(50)    NOT NULL    UNIQUE,
    first_name      VARCHAR2(10)    NOT NULL,
    last_name       VARCHAR2(10)    NOT NULL,
    CONSTRAINT users_pk PRIMARY KEY (user_id)
)


CREATE TABLE product 
(
    product_id      NUMBER,
    product_name    VARCHAR2(50)    NOT NULL,
    CONSTRAINT product_pk PRIMARY KEY (product_id)
)

CREATE TABLE downloads
(
    download_id     NUMBER, 
    user_id     NUMBER  NOT NULL,
    product_id NUMBER   NOT NULL,
    download_date   DATE NOT NULL,
    filename    VARCHAR2(50)    NOT NULL,
    CONSTRAINT downloads_pk PRIMARY KEY (download_id),
    CONSTRAINT downloads_fk 
        FOREIGN KEY(user_id) REFERENCES users (user_id),
    CONSTRAINT downloads_fk2
        FOREIGN KEY(product_id) REFERENCES product(product_id)
)

CREATE SEQUENCE user_id_seq
CREATE SEQUENCE download_id_seq
CREATE SEQUENCE product_id_seq

downloads 表连接用户和产品表，包含外键 user_id 和 product_id。我正在尝试通过以下代码将数据插入下载表:

INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NEXTVAL,1,2, SYSDATE, 'one_horse_town.mp3')
INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NXTVAL, 2, 1, SYSDATE, 'pedals_are_falling.mp3')
INSERT INTO downloads (download_id,user_id,product_id,download_date,filename)
    VALUES(download_id_seq.NEXTVAL, 2, 2, SYSDATE, 'random_song.mp3')

Oracle 然后给我以下错误:

Error report - ORA-02291: integrity constraint (BC29369.DOWNLOADS_FK2) violated - parent key not found.

我不确定为什么会这样，因为在我看来我已经清楚地标记了主键和外键关系。有谁知道如何解决这一问题？提前致谢!

Answer 1

约束

CONSTRAINT downloads_fk FOREIGN KEY(user_id) REFERENCES users (user_id)

和

CONSTRAINT downloads_fk2 FOREIGN KEY(product_id) REFERENCES product(product_id)

表示 user_id 值 1 和 2，product_id 值 1 和 2，您尝试插入到 downloads 表中必须已经存在于users 表和product 表中。

如果在将这些值插入到 downloads 表之前，users 和 product 表未填充这些值(1 和 2)，您将出现此错误，因为将违反完整性约束。

您正在创建一个没有父记录的孤立记录。这正是您在创建这两个约束时要确保不会发生的事情。