问题：
未正确使用外键的SQL错误“未找到父键”？
佟嘉祯
我更新了我的尝试，将一些内容保留为CHAR，但仍然得到一个类似的错误：第1行错误：ORA-02091：事务回滚ORA-02291：违反完整性约束（MMM1339.ITEMNO_PHAR_FK）-未找到父密钥
需求的外键都在不同的供应主键中有值，所以我不确定为什么仍然存在问题。
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;
共有3个答案
茹高义
我们需要一些重排来抑制ORA-02291错误：
>
创建没有FOREIGN KEYS的表，并且在创建完所有表之后，ALTER表以包含FOREIGN KEYS。为了抑制INSERT操作过程中的数据不一致，我们需要将FOREIGN KEYS创建为DEFERRABLE INITIally DEFERRED（通过以下方式，即使发出结束COMMIT，我们也不会遇到任何错误）。
我认为，最好将SUPPLIERNO列存储为INT（EGER）或NUMBER，而不是CHAR。此外，CHAR数据类型已弃用，最好将其转换为VARCHAR2。
因此，使用以下一系列操作（在底部，我已经更改了REQUISITION的一些值，以不违反外国约束）：
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;
全誉
编辑：
将SUPPLIES\u SURGICAL和SUPPLIES\u NONSURGICAL合并为一个SUPPLIES表，并将PHARMA\u剂量定义为该表下的子表。
现在，SUPPLIES行必须是手术行或非手术行，可以实现为Y/N标志，并通过检查约束强制执行。（不过，这可能存在的一个问题是，该模型不会阻止您为手术项目添加剂量。）
在下面，我将ITEMNO设置为整数，并修复了CHAR列。您可以考虑查看在
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');
罗华翰
不幸的是（对于您的DDL代码），我不得不同意@William Robertson的观点——您需要更改您的模型，因此，您需要完全重新编写DDL代码。原因如下：
查看一个反向工程模型，从您的原始DDL代码中，我们可以看到请购单有3个（对不起，4个）父表。这就是为什么它的插入总是失败，因为外键冲突。您的型号：
一个以DDL代码形式说明问题的简化示例可能如下所示：
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 ;
/
因此，在填充了父表后，只需快速检查一下：
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
都很好。现在我们要在子表中插入一些行。
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
您可以看到正确的父表不仅仅是“自动被选中”。
在William的模型Oto中，请购单只有一个与“供应品”相关的父项（表）。这将使插入行更容易。。。见下文。
类似资料：
-
我将在7周内浏览7个数据库。 在 PostgreSQL 中，我创建了一个具有串行venue_id列的地点表。 < code>\d场馆的输出 下一步是创建一个用外键引用venue_id的事件表。 我在尝试这个: 我得到这个错误： 怎么了？
-
我克隆了一个Android项目： 当我试图用android studio打开app文件夹以开始使用该项目时，我发现Gradle“app”项目刷新失败，错误：未找到SDK位置。使用sdk定义位置。本地目录。属性文件或使用ANDROID\u HOME环境变量。问题是，SDK位置已在local.properties中设置： 这与我在这台机器上使用的其他android项目中的位置相同。那么，为什么我会出现
-
我正在尝试使用spark sql运行一个基本的java程序 如果有人能给我指出一些在斯巴克-sql（斯巴克-2.1.1）上可以阅读的好材料，那也太好了。我计划使用火花来实现ETL，连接到MySQL和其他数据源。 异常在线程"main"org.apache.spark.sql.分析异常：未找到表或视图：；第1行pos 21；
-
我遇到了一些SQL外键的问题。 下面是我的表和插入SQL。 SQL ORA-02291:违反完整性约束-未找到父键 我不知道为什么我的外键是错的？
-
我试图从MySQL移动到Oracle和我的一个查询最初看起来像这样 然而，当我在ORACLE中运行同一个查询时，只做了一个小改动。。。 它给了我以下错误： SQL错误：ORA-00933:SQL命令未正确结束00933。00000-“SQL命令未正确结束” Oracle SQL Developer工具强调了声明部分的一个问题 作为t1 我读了一些其他的问题，提到在Oracle语句的某些部分周围放置
-
问题内容： 当我输入这个 我收到关注错误 ORA-00933：SQL命令未正确结束 问题答案： 在Oracle中，您不能像这样指定多组值（我猜您来自允许该语法的MySQL背景）。最简单的方法是生成多个语句 您还可以使用单个语句插入多行