SQL 数据库错误，找不到父键

Question

我的sql数据库中有几个错误，我正试图弄清楚为什么某些行会给我一个错误。我不明白为什么有些行没有父关键字，而其他行有。如果有任何反馈，我将不胜感激。

DROP TABLE Ordering CASCADE CONSTRAINTS;
DROP TABLE Customer CASCADE CONSTRAINTS;
DROP TABLE Warehouse_Fullfillment CASCADE CONSTRAINTS;
DROP TABLE Shipment_Preference CASCADE CONSTRAINTS;
DROP TABLE Delivery CASCADE CONSTRAINTS;
DROP TABLE Returns CASCADE CONSTRAINTS; 
CREATE TABLE Ordering
( 
customer_id     NUMBER          NOT NULL,
invoice_id      VARCHAR2 (50) NOT NULL,
payment_total   NUMBER        NOT NULL,
Order_Number    NUMBER        NOT NULL
);
CREATE TABLE Customer
(
customer_id                     NUMBER          NOT NULL,
customer_address1               VARCHAR2(50),
customer_contact_last_name      VARCHAR2(50),
customer_contact_first_name     VARCHAR2(50),
customer_city                   VARCHAR2(50)    NOT NULL,
customer_state                  VARCHAR2(50)         NOT NULL,
customer_zip_code               VARCHAR2(20)    NOT NULL,
customer_phone                  VARCHAR2(50),
default_account_number          NUMBER          NOT NULL,
Refund_Invoice_id      VARCHAR2 (50)  NOT NULL,
Tracking_Code          Number         NOT NULL

);
CREATE TABLE Warehouse_Fullfillment
(
Item_id                     Number          NOT NULL,
Invoice_id                  VARCHAR2 (50)   NOT NULL,
Item_Scanned                VARCHAR2 (50)   NOT NULL,               
Order_Completion_Average    NUMBER          NOT NULL,
Damaged_Item_id             NUMBER          NOT NULL,
Tracking_Code              Number        NOT NULL

);
CREATE TABLE Shipment_Preference 
(
Status_id                  VARCHAR2 (50) NOT NULL,
Item_id                    Number NOT NULL,
Shipment_type              Varchar2 (50) NOT NULL,
invoice_id      VARCHAR2 (50) NOT NULL
);
CREATE TABLE Delivery
(
return_id                  VARCHAR2 (50) NOT NULL,
Item_id                    Number NOT NULL,
Customer_id                VARCHAR2 (50) NOT NULL,
date_of_desination         DATE        NOT NULL,
Tracking_Code              Number        NOT NULL,
Refund_Invoice_id        VARCHAR2 (50)  NOT NULL
);
CREATE TABLE Returns
( 
Refund_Invoice_id        VARCHAR2 (50)  NOT NULL,
Tracking_Code            Number         NOT NULL,
return_id                  VARCHAR2 (50) NOT NULL,
customer_id     NUMBER          NOT NULL
);
Alter Table Ordering 
ADD Constraint Ordering_pk PRIMARY KEY (invoice_id); 
Alter TABLE Customer
ADD Constraint Customer_pk  PRIMARY KEY ( customer_id);
Alter Table Warehouse_Fullfillment
ADD CONSTRAINT  Warehouse_Fullfillment_pk PRIMARY KEY( Item_id);
Alter Table Shipment_Preference 
ADD CONSTRAINT Shipment_Preference_pk Primary KEY (Status_id);
Alter Table Delivery
ADD Constraint Delivery_pk PRIMARY KEY (Tracking_Code);
Alter Table Returns
ADD CONSTRAINT Returns_pk PRIMARY KEY (Refund_Invoice_id);

INSERT INTO Customer VALUES (1, 'Steve', 'Rodriquez','salt lake', '3652 CERMAK', 'Georgia', '60087', '2744808991', 48156, 252762, 206806541);
INSERT INTO Customer VALUES (2, 'Faye', 'Lawrence', 'memcity','59528 BALBO', 'Colorado', '23714', '4994745250', 25215, 9948784, 578883648);
INSERT INTO Customer VALUES (3, 'Fernando', 'Poole', 'lolcity','1693 RANDOLPH SUB', 'North Dakota', '67529', '4994745250', 35293, 9787943, 893371225);
INSERT INTO Customer VALUES (4, 'Raul', 'Weaver', 'dsad','8898 OHIO', 'Texas', '27709', '5026705519', 42542, 915335, 689553023);
INSERT INTO Customer VALUES (5, 'Neil', 'Maldonado','opdaddy', '74965 NORTH', 'Arizona', '94301', '1593029054', 57700, 8468823, 918257595);
INSERT INTO Customer VALUES (6, 'Patrick', 'Daniels','poking', '19 FOREMAN', 'Pennsylvania', '60656', '6433389900', 44919, 3593503, 118768693);
INSERT INTO Customer VALUES (7, 'Nina', 'James', 'fafe', '16 OAK', 'Utah', '78348', '2892496384', 5795, 575950, 575426221);
INSERT INTO Customer VALUES (8, 'Armando', 'Santiago','code', '1459 SOLIDARITY', 'Illinois', '36799', '2097829672', 57455, 1700328, 376418353);
INSERT INTO Customer VALUES (9, 'Pat', 'Bates', 'bath','99 BELLEVUE', 'Alaska', '35860', '1918078171', 82772, 4682474, 433556915);
INSERT INTO Customer VALUES (10, 'Pat', 'Bowman', 'bandlecity','8898 ERIE', 'South Carolina', '55474', '1034729258', 80151, 5235811, 462502003);

INSERT INTO Ordering VALUES (1, 14364, '566', 820859);
INSERT INTO Ordering VALUES (2, 43017, '662', 228408);
INSERT INTO Ordering VALUES (3, 43317, '38', 754554);
INSERT INTO Ordering VALUES (4, 44976, '516', 801963);
INSERT INTO Ordering VALUES (5, 67918, '275', 493792);
INSERT INTO Ordering VALUES (6, 75683, '395', 636601);
INSERT INTO Ordering VALUES (7, 13120, '926', 893760);
INSERT INTO Ordering VALUES (8, 37175, '481', 462599);
INSERT INTO Ordering VALUES (9, 77809, '486', 88513);
INSERT INTO Ordering VALUES (10, 4078, '89', 668256);
INSERT INTO Shipment_Preference  VALUES (443863, 43432, 14364, 'Prime');
INSERT INTO Shipment_Preference  VALUES (272943, 52525, 43017, 'Standard');
INSERT INTO Shipment_Preference  VALUES (30951, 52355, 43317, 'Prime');
INSERT INTO Shipment_Preference  VALUES (235283, 64266, 44976, 'Standard');
INSERT INTO Shipment_Preference  VALUES (44934, 75753, 67918, 'Prime');
INSERT INTO Shipment_Preference  VALUES (588629, 54263, 75683, 'Standard');
INSERT INTO Shipment_Preference  VALUES (542942, 86573, 13120, 'Standard');
INSERT INTO Shipment_Preference  VALUES (960423, 56316, 37175, 'Standard');
INSERT INTO Shipment_Preference  VALUES (347727, 78526, 77809, 'Standard');
INSERT INTO Shipment_Preference  VALUES (993732, 97665, 4078, 'Prime');

INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (43432,14364, 'Yes', 206806541, 0.63, 54117);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (52525, 43017, 'Yes', 578883648, 0.31, 81023);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (52355, 43317, 'Yes', 893371225, 0.4, 68759);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (64266, 44976, 'Yes', 689553023, 0.4, 47751);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (75753, 67918, 'Yes', 918257595, 0.3, 7507);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (54263, 75683, 'Yes', 118768693, 0.1, 23178);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (86573, 13120, 'Yes', 575426221, 0.71, 7126);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (56316, 37175, 'Yes', 376418353, 0.97, 85478);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (78526, 77809, 'Yes', 433556915, 0.02, 99696);
INSERT INTO WAREHOUSE_FULLFILLMENT VALUES (97665, 4078, 'Yes', 462502003, 0.85, 33137);

INSERT INTO Delivery  VALUES (535532, 1, 43432, TO_DATE(' 12/14/2018', 'MM/DD/YYYY'), 206806541, 252762);
INSERT INTO Delivery  VALUES (525545, 2, 52525, TO_DATE(' 12/14/2018', 'MM/DD/YYYY'), 578883648, 9948784);
INSERT INTO Delivery  VALUES (542526, 3, 52355, TO_DATE(' 12/14/2018', 'MM/DD/YYYY'), 893371225, 9787943);
INSERT INTO Delivery  VALUES (777777,4, 64266, TO_DATE(' 12/27/2018', 'MM/DD/YYYY'), 689553023, 9153351);
INSERT INTO Delivery  VALUES (888888,5, 75753, TO_DATE(' 12/18/2018', 'MM/DD/YYYY'), 918257595, 8468823);
INSERT INTO Delivery  VALUES (999999,6, 54263, TO_DATE(' 12/22/2018', 'MM/DD/YYYY'), 118768693, 3593503);
INSERT INTO Delivery  VALUES (111111,7, 86573, TO_DATE(' 12/20/2018', 'MM/DD/YYYY'), 575426221, 575950);
INSERT INTO Delivery  VALUES (101010,8, 56316, TO_DATE(' 12/22/2018', 'MM/DD/YYYY'), 376418353, 1700328);
INSERT INTO Delivery  VALUES (010101,9, 78526, TO_DATE(' 12/21/2018', 'MM/DD/YYYY'), 433556915, 4682474);
INSERT INTO Delivery  VALUES (524531,10, 97665, TO_DATE(' 12/15/2018', 'MM/DD/YYYY'), 462502003, 5235811);
INSERT INTO Returns VALUES (1, 535532, 2527621, 206806541);
INSERT INTO Returns VALUES (2, 525545, 9948784, 578883648);
INSERT INTO Returns VALUES (3, 542526, 9787943, 893371225);
INSERT INTO Returns VALUES (4, 777777, 9153351, 689553023);
INSERT INTO Returns VALUES (5, 888888, 8468823, 918257595);
INSERT INTO Returns VALUES (6, 999999, 3593503, 118768693);
INSERT INTO Returns VALUES (7, 111111, 575950, 575426221);
INSERT INTO Returns VALUES (8, 101010, 1700328, 376418353);
INSERT INTO Returns VALUES (9, 010101, 4682474, 433556915);
INSERT INTO Returns VALUES (10, 524531, 5235811, 462502003);
Alter Table Ordering
ADD CONSTRAINT order_fk_Preference FOREIGN KEY (Customer_id) REFERENCES Customer (Customer_id);
Alter Table Warehouse_Fullfillment
ADD CONSTRAINT Warehouse_Fullfil_fk FOREIGN KEY (Invoice_id) REFERENCES Ordering (Invoice_id) ;
Alter Table Shipment_Preference
ADD CONSTRAINT Shipment_fk_Preference FOREIGN KEY (Item_id) REFERENCES Warehouse_Fullfillment (Item_id) ;

Alter Table Shipment_Preference
ADD CONSTRAINT Delivery_fk_Preference FOREIGN KEY (invoice_id) REFERENCES Ordering (Invoice_id);
Alter Table Returns
ADD CONSTRAINT Returns_fk_Preference Foreign Key (Tracking_code) REFERENCES Delivery(Tracking_code);
Alter Table Returns
ADD CONSTRAINT Customer_fk_Preference Foreign Key (Customer_id) References Customer (Customer_id);
Alter Table Delivery 
ADD CONSTRAINT DELIVERY_fk_o_preference Foreign key(Item_id) References Warehouse_Fullfillment (Item_id);
Alter Table Delivery 
Add constraint delivery_fk_12_pref Foreign Key (Refund_Invoice_id) References Returns (Refund_Invoice_id);
Alter Table Customer
Add Constraint customer_fk_12_pref Foreign Key (Tracking_code) References Delivery(Tracking_Code);
Alter Table Warehouse_Fullfillment
Add Constraint Warehouse_full_fk_35_pref Foreign Key (Tracking_Code) References Delivery (Tracking_Code);

命令中第173行开始时出错-更改表发货_参考添加约束交货_ fk_参考外国KEY(invoice_id(REFERENCES Ordering(invoice_id(错误报告-ORA-02298:无法验证(CIS255A28.DELIVERY_FK_PREFERENCE(-父级找不到密钥02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

命令中第176行开始时出错-Alter Table返回ADDCONSTRAINT Returns_fk_Preference外键(Tracking_code(REFERENCES交付(Tracking_code(错误报告-ORA-02298:无法validate(CIS255A28.RETURNS_FK_PREREFERENCE(-未找到父密钥02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

命令中第179行开始时出错-Alter Table返回ADDCONSTRAINT Customer_fk_Preference外键(Customer_id(引用Customer(Customer_id(错误报告-ORA-02298:无法验证(CIS255A28.CUSTOMER_FK_PREFERERENCE(-未找到父密钥02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

在命令中的第182行开始时出错-Alter Table Delivery ADDCONSTRAINT DELIVERY_fk_o_preference外键(Item_id(引用Warehouse_Fullfilment(Item_id(错误报告-ORA-02298:无法validate(CIS255A28.DELIVERY_FK_O_PREFERENCE(-未找到父密钥02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

在命令的第185行开始时出错-Alter Table Delivery Addconstraint delivery_fk_12_pref外键(Refund_Invoice_id(引用返回(Refund_Invoice_id(错误报告-ORA-02298：无法验证(CIS255A28.DELIVERY_FK_12_PREF(-父密钥不是建立02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

在命令"Alter Table"的第191行开始时出错Warehouse_Fullfilment添加约束Warehouse_full_fk_35_pref外键(Tracking_Code(引用传递(Tracking_Code(错误报告-ORA-02298:无法验证(CIS255A28.WAREHOUSE_FFULL_FK_35_PREF(-未找到父密钥02298.00000-"无法验证(%s.%s(-找不到父密钥"*原因：alter表验证约束失败，因为该表具有子记录。*行动：明显的

Answer 1

这些错误意味着父表和子表中的数据不同步，引用完整性没有得到维护。

例如，如果您注意到示例中的第一个错误。此错误意味着您的Shipment_Preference表中有Invoice_id，但在Ordering表中不存在。因此，无法应用外键。如果可能的话，您必须删除那些孤立的记录或更新它们。