时间戳

首页 > 解决方案 > ORA-02291 错误。不确定是什么导致了问题

sql - ORA-02291 错误。不确定是什么导致了问题

问题描述

一切运行良好。结束部分给出ORA-02291错误。不确定是什么导致了该错误，因为我认为所有代码都运行良好。这是在 Oracle SQL 开发人员中制作的，错误消息是 ORA-02291。我希望有人能给我一个答案。请帮我。

代码：


CREATE TABLE comm_customer
( 
Customer_Id int NOT NULL,
Customer_Name VARCHAR(50) NOT NULL,
Address VARCHAR(255) NOT NULL,
Billing_Address VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
country VARCHAR(255) NOT NULL, 
phone INT NOT NULL,
primary key(Customer_id)
);

CREATE TABLE comm_orders
(
order_Id INT NOT NULL,
Customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(5,2) NOT NULL,
Order_status VARCHAR(20) NOT NULL,
primary key(order_id),
FOREIGN KEY (customer_id ) REFERENCES comm_customer(customer_id)
);

CREATE TABLE comm_products
( 
product_id INT NOT NULL, 
product_name VARCHAR(255) NOT NULL,
product_price decimal(5,2) NOT NULL,
product_quantity decimal(5,2) NOT NULL,
product_status VARCHAR(255) NOT NULL, 
customer_id int NOT NULL, 
primary key(product_id),
FOREIGN KEY (customer_id ) REFERENCES comm_customer(customer_id)
);

CREATE TABLE comm_shipments
(
shipment_id INT NOT NULL,
order_id INT NOT NULL,
shipment_date DATE NOT NULL,
PRIMARY KEY (shipment_id),
FOREIGN KEY (order_id ) REFERENCES  comm_orders(order_id)
);

CREATE TABLE comm_shopping_cart
( 
orderdetails_id INT NOT NULL,
order_id INT NOT NULL,
product_id int NOT NULL,
quantity int NOT NULL,
price decimal(5,2),
primary key(orderdetails_id),
FOREIGN KEY (order_id ) REFERENCES comm_orders(order_id),
FOREIGN KEY (product_id ) REFERENCES comm_products(product_id)

);

--For Table Comm_customer
insert into comm_customer values(1011, 'John', '48 Maple Heights Road', '48 Maple Heights Road', 'Toronto', 'Canada', 9988766779);
insert into comm_customer values(1012, 'James', '32 St.Jordan Cressent', '32 St.Jordan Cressent', 'Chennai', 'India', 9988722779);
insert into comm_customer values(1013, 'Anderson', '5 Thornway Street', '#1755 JBS Colony', 'Surat', 'India', 9988123779 );
insert into comm_customer values(1014, 'Jose', '88 Greenbelt Drive', '#1983 ABS Nagar', 'Mumbai', 'India', 9988766885 );
insert into comm_customer values(1015, 'Leo', '#1765 XSX Nagar', '#10993 ACD Nagar', 'Hoshiarpur', 'India', 9955466779 );

--For Table Comm_products:
INSERT into comm_products values(01, 'XI Phone', 250, 4, 'checked out', 1015);
INSERT into comm_products values(02, 'Book', 120, 2, 'not checked out', 1011); 
INSERT INTO comm_products values(03, 'Vegetable',100, 5, 'not checked out', 1012); 
INSERT into comm_products values(04, 'Biscuits', 250, 1, 'checked out', 1013);
INSERT into comm_products values(05, 'Shampoo', 500, 2, 'not checked out', 1014); 

--For table Comm_orders:
insert into comm_orders values(11, 1014, '2020-MAY-01', 500, 'checked out'); 
insert into comm_orders values(12, 1011, '2019-JUL-18', 140, 'not checked out');
insert into comm_orders values(13, 1013, '2020-JAN-31', 170, 'checked out'); 
insert into comm_orders values(14, 1012, '2019-FEB-15', 120, ' not checked out');
insert into comm_orders values(15, 1011, '2018-JUN-21', 600, 'checked out');


--For table comm_shippments:
insert into comm_shipments values(001, 13, '2020-FEB-05');
insert into comm_shipments values(002, 15, '2018-JUL-01');
insert into comm_shipments values(003, 12, '2019-07-25'); 
insert into comm_shipments values(004, 11, '2020-MAY-05');
insert into comm_shipments values(005, 14, '2019-FEB-25'); 

--For table comm_shopping_cart:
insert into comm_shopping_cart values(701, 01, 11, 4, 250);
insert into comm_shopping_cart values(702, 02, 12, 3, 120);
insert into comm_shopping_cart values(704, 03, 13, 6, 100);
insert into comm_shopping_cart values(703, 04, 14, 2, 250);
insert into comm_shopping_cart values(705, 05, 15, 1, 500);

标签： sqloracleforeign-keysconstraints

解决方案

好的，所以你说出错的语句是这个

insert into comm_shopping_cart values(701, 01, 11, 4, 250);

我强烈敦促您列出insert声明中的列。这有助于记录事情，这样人们就不必经常参考表定义来查看定义的列的顺序。这意味着将来当您添加新列时，您的语句不会突然失败。

insert into comm_shopping_cart( orderdetails_id,
                                order_id,
                                product_id,
                                quantity,
                                price) 
  values(701, 
          01, 
          11, 
           4, 
         250);

好的，这样做之后，错误是找不到父键。您的实际错误消息应包含约束的名称，该名称会告诉您哪一列是问题所在。你没有给我们这些信息，所以我们必须弄清楚。上有两个外键comm_shopping_cart，theorder_id和 the product_id。那么哪个不存在呢？

您正在尝试插入一行order_id01 （我不明白为什么您将前导 0 放在数字列中）。但是表中只有11 到 15 之间order_id的值。comm_orders

您还尝试插入 aproduct_id为 11 的行。但您的表中只有product_id1 到 5 之间的值comm_products。所以两个外键约束都会失败。

我的猜测是您打算在语句中指定一个order_id11 和一个product_id1insert并颠倒列的顺序。由于您没有在insert语句中包含列列表，因此您自己调试问题变得更加困难，因为您的insert语句不是自我记录的。如果您指定了列列表，您还可以按照您想要的任何顺序列出列，所以如果您想在您可以拥有的product_id之前指定。order_id

推荐阅读