ORA-02291：完整性约束（OPS$P2417335.VOD_FILM_CLASS_FK）被侵犯-未找到父密钥

Question

我在向vod_film表中插入值时遇到问题。各表如下：

CREATE TABLE vod_classification (
dbClassId       CHAR(3) NOT NULL,
dbDescription       VARCHAR2(150),
CONSTRAINT vod_classification_PK PRIMARY KEY (dbClassId)
);

CREATE TABLE vod_film (
dbFilmId          NUMBER(4) NOT NULL,
dbTitle           VARCHAR2(100) NOT NULL,
dbDirector_firstname  VARCHAR2(50) NOT NULL,
dbDirector_lastname   VARCHAR2(50) NOT NULL,
dbGenre               VARCHAR2(20),
dbUK_release_date     DATE,
dbFilename            VARCHAR2(50),
dbRuntime         NUMBER(4),
dbClass               CHAR(3),
CONSTRAINT vod_film_PK PRIMARY KEY (dbFilmId),
CONSTRAINT vod_film_class_FK FOREIGN KEY (dbClass) REFERENCES vod_classification (dbClassId)
ON DELETE SET NULL
);

CREATE TABLE vod_actor (
dbActorId      CHAR(4) NOT NULL,
dbFirstname    VARCHAR2(50) NOT NULL,
dbLastname     VARCHAR2(50) NOT NULL,
dbDateOfBirth  DATE,
dbNationality  VARCHAR2(30),
dbBiography    CLOB,
CONSTRAINT vod_actor_PK PRIMARY KEY (dbActorId)
);

CREATE TABLE vod_role (
dbFilmId           NUMBER(4) NOT NULL,
dbActorId          CHAR(4) NOT NULL,
dbCharacterName    VARCHAR2(25) NOT NULL,
dbFirstAppearance  NUMBER(6),
dbDescription      CLOB,
CONSTRAINT vod_role_PK PRIMARY KEY (dbFilmId, dbActorId, dbCharacterName),
CONSTRAINT vod_role_film_FK FOREIGN KEY (dbFilmId) REFERENCES vod_film (dbFilmId)
ON DELETE CASCADE,
CONSTRAINT vod_role_actor_FK FOREIGN KEY (dbActorId) REFERENCES vod_actor (dbActorId)
ON DELETE CASCADE
);

当我运行以下代码时：

INSERT INTO vod_film (dbFilmId, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFilename, dbRuntime, dbClass)
VALUES (1, 'Toy Story 3', 'lee', 'unkrich', 'Comedy', '19-JUL-2010', 'ToyStory3.mpg', 103, 'U');

我有这个错误返回：

ORA-02291：完整性约束（OPS$P2417335.VOD_FILM_CLASS_FK）被侵犯-未找到父密钥

我不确定为什么会返回此错误，因为外键引用了vod_分类中的现有字段。在插入数据时，所有表格都已按我认为正确的顺序添加。

任何关于我为什么会收到这个错误的帮助都将不胜感激，或者如果我需要提供任何进一步的信息，请告诉我。

祝好

史提芬。

Answer 1

我认为信息非常清楚。约束条件是：

CONSTRAINT vod_film_class_FK FOREIGN KEY (dbClass)
    REFERENCES vod_classification (dbClassId)
    ON DELETE SET NULL

错误表示您正在添加值dbClass（即“U”）。此值不在vod\u分类中。dbClassId。

您可以检查该值是否存在：

select c.*
from vod_classification c
where c.dbClassId = 'U';