问题描述
我更新了尝试,将某些内容保留为CHAR,但仍然收到类似的错误:第1行的错误:ERRORORA-02091:交易已回滚ORA-02291:违反完整性约束(MMM1339.ITEMNO_PHAR_FK)-找不到父密钥
I updated my attempt, keeping some things as CHAR's and still get a similar error:ERROR at line 1:ORA-02091: transaction rolled backORA-02291: integrity constraint (MMM1339.ITEMNO_PHAR_FK) violated - parent key not found
请购单的外键在不同的供应主键中都具有值,因此我不确定为什么仍然存在问题.
Requisition's foreign keys all have values in the different supply primary keys, so I'm not sure why there is still an issue.
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;
推荐答案
不幸的是(对于您的DDL代码)我必须同意@William Robertson-您需要更改模型,因此,您需要重新编写DDL代码完全地.原因如下:
Unfortunately (for your DDL code) I have to agree with @William Robertson - you need to change your model, and thus, you need to rework your DDL code completely. Reasons for this being as follows:
从原始DDL代码来看,通过反向工程模型,我们可以看到REQUISITION有3个(很抱歉,有4个)父表.因此,由于违反外键,其插入总是失败.您的模型:
Looking at a reverse-engineered model, from your original DDL code, we can see that REQUISITION has 3 (sorry, 4) parent tables. That's why its inserts always fail, due to foreign key violations. Your model:
一个简化的示例以DDL代码的形式说明了问题,看起来像这样:
A simplified example, illustrating the problem in form of DDL code, could look something like this:
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 ;
/
因此,在填充父表之后,只需快速检查一下即可:
So, with our parent tables populated, just a quick check:
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
一切都很好.现在我们要在子表中插入一些行.
All good. Now we want to insert some rows into our child table.
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
您会看到正确的父表不只是自动被选择".
You see that the correct parent table does not just "get picked automatically".
在威廉(William)的模型OTOH中,需求"仅具有一个父项(表).哪个应该使插入行容易得多...请参见下文.
In William's model OTOH, REQUISITION has only one parent (table) with respect to "supplies". Which should make inserting rows much easier ... see below.
这篇关于错误使用外键的SQL错误“找不到父键"?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!