可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
I updated my attempt, keeping some things as CHAR's and still get a similar error:ERROR at line 1:
ORA-02091: transaction rolled back
ORA-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;
回答1:
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:
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:
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".
In William's model OTOH, REQUISITION has only one parent (table) with respect to "supplies". Which should make inserting rows much easier ... see below.
回答2:
Edited:
Combine SUPPLIES_SURGICAL and SUPPLIES_NONSURGICAL as a single SUPPLIES table, and define PHARMA_DOSAGE as a child table under that.
Now SUPPLIES rows must be either surgical or not, which can be implemented as a Y/N flag and enforced by a check constraint. (One possible problem with this, though, is that the model does not prevent you from adding a dosage for a surgical item.)
In the following, I've made ITEMNO an integer and fixed your CHAR columns. You might consider reviewing the datatype of all columns ending in NO, especially if they are going to be generated sequentially. I would also make all generated PK columns identity columns, but I'll leave the details up to you.
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');
回答3:
we need some rearrangements to suppress ORA-02291 error :
- First of all, the INSERT statements should be ended with
semi-colons.
- There should exist a table called SUPPLIER to be referenced.
Create tables without FOREIGN KEYS, and after all of them created,
ALTER tables to include FOREIGN KEYS.
To Suppress data-inconsistency during INSERT operations, we need to create FOREIGN KEYS as DEFERRABLE INITIALLY DEFERRED(with the following way,we won't encounter any error even if when the ending COMMIT issued ).
I think, you'd better storing SUPPLIERNO columns as INT(EGER) or NUMBER, instead of CHAR. Besides, CHAR data type is deprecated, it's better to convert them to VARCHAR2.
So, use the following series of action ( At the bottom part, I've changed some values of REQUISITION not to violate Foreign Constraints ):
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;
回答4:
This answer relates to your question about why your updated definition with deferred constraints still gives an FK violation.
After inserting the rows but before attempting to commit:
select r.reqno, r.itemno
, sp.name as pharmaceutical
, ss.name as surgical
, sn.name as nonsurgical
from requisition r
left join supplies_pharmaceutical sp on sp.itemno = r.itemno
left join supplies_surgical ss on ss.itemno = r.itemno
left join supplies_nonsurgical sn on sn.itemno = r.itemno;
REQNO ITEMNO PHARMACEUTICAL SURGICAL NONSURGICAL
------- ------- --------------- --------------- ---------------
000001 888520 Cart
000002 923956 Zupridol
000003 054802 Scalpel
Item 888520 violates constraint ITEMNO_PHAR_FK because it is not defined in parent table SUPPLIES_PHARMACEUTICAL:
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_PHAR_FK
FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_PHARMACEUTICAL(ITEMNO) ...
It also violates ITEMNO_SURG_FK because 888520 is not in parent table SUPPLIES_SURGICAL:
ALTER TABLE REQUISITION ADD CONSTRAINT ITEMNO_SURG_FK
FOREIGN KEY(ITEMNO) REFERENCES SUPPLIES_SURGICAL(ITEMNO) ...
And so on. Each requisition row violates two constraints. Defining the constraints as deferred doesn't change anything except the timing of the error.
To satisfy your FK definitions, every itemno would need to exist in all three supplies tables.
If you really need to have three supplies tables, you will need three separate supply item columns, each with its own FK to the corresponding table.
(And you really should change those CHAR columns to the standard string type. CHAR has no place in an Oracle database, it's purely for weird portability and ANSI completeness requirements, not a type you are actually supposed to use.)
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://i.stack.imgur.com/mUPSK.png', '推荐 再贱就再见 的文章《SQL Error 'Parent Key Not Found' for Impro》','https://www.manongdao.com/article-1268344.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}