具有 3 个表和 "no matching unique or primary key for this column" 的简单数据库

Question

我有三个表，其中两个是独立创建的，第三个表的创建包括前两个表的一些输入。前两个表没有问题，但是，当我试图创建第三个表时，我得到了一个错误：

Error report -
ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
catalog view

问题是，我通过复制/粘贴前两个表的列名/定义创建了第三个表，但我仍然收到这个荒谬的错误消息。现在我想知道列的顺序，尤其是约束的顺序是否重要。

表格：

1. comm_Customers

CREATE TABLE comm_Customers (
custID NUMBER(6) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
LastName VARCHAR2(15) NOT NULL,
HomeCountry VARCHAR2(2) NOT NULL,
HomeState_Prov VARCHAR2(2) NOT NULL,
HomeCity VARCHAR2(20) NOT NULL,
HomeAddress VARCHAR2(25) NOT NULL,
Phone NUMBER(10) NOT NULL,
Email VARCHAR2(15) NOT NULL,
ShippCountry VARCHAR2(2) NOT NULL,
ShippState_Prov VARCHAR2(2) NOT NULL,
ShippCity VARCHAR2(10) NOT NULL,
ShippAddress VARCHAR2(15) NOT NULL,  
CONSTRAINT comm_customers_custid_pk PRIMARY KEY (custID)    
);

2. comm_Items

CREATE TABLE comm_Items (
itemID NUMBER(4) NOT NULL,
ItemCat VARCHAR2(3) NOT NULL,
ItemQty NUMBER(4) NOT NULL,
SalePrice NUMBER(6,2) NOT NULL,
CostPrice NUMBER(6,2) NOT NULL,
ItemDesc VARCHAR2(15),
CONSTRAINT comm_items_itemid_pk PRIMARY KEY (itemID)
);

3. comm_Orders，给出错误

CREATE TABLE comm_Orders (
orderID NUMBER(10) NOT NULL,
OrderQty NUMBER(4) NOT NULL,
OrderDate DATE NOT NULL,
Shipped VARCHAR2(1),
ShippedDate DATE,    
custID NUMBER(6) NOT NULL,
Phone NUMBER(10) NOT NULL,
Email VARCHAR2(15) NOT NULL,
ShippCountry VARCHAR2(2) NOT NULL,
ShippState_Prov VARCHAR2(2) NOT NULL,
ShippCity VARCHAR2(10) NOT NULL,
ShippAddress VARCHAR2(15) NOT NULL,    
itemID NUMBER(4) NOT NULL,    
SalePrice NUMBER(6,2) NOT NULL,

CONSTRAINT comm_order_orderid_pk PRIMARY KEY (orderID),

CONSTRAINT comm_order_custid_fk FOREIGN KEY (custID) 
REFERENCES comm_Customers(custID),

CONSTRAINT comm_order_phone_fk FOREIGN KEY (Phone) 
REFERENCES comm_Customers(Phone),

CONSTRAINT comm_order_email_fk FOREIGN KEY (Email) 
REFERENCES comm_Customers(Email),

CONSTRAINT comm_order_shippcountry_fk FOREIGN KEY (ShippCountry) 
REFERENCES comm_Customers(ShippCountry),

CONSTRAINT comm_order_shippstate_prov_fk FOREIGN KEY (ShippState_Prov) 
REFERENCES comm_Customers(ShippState_Prov),
CONSTRAINT comm_order_shippcity_fk FOREIGN KEY (ShippCity) 
REFERENCES comm_Customers(ShippCity),
CONSTRAINT comm_order_shippaddress_fk FOREIGN KEY (ShippAddress) 
REFERENCES comm_Customers(ShippAddress),     

CONSTRAINT comm_order_itemid_fk FOREIGN KEY (itemID) 
REFERENCES comm_Items(itemID), 

CONSTRAINT comm_order_saleprice_fk FOREIGN KEY (SalePrice) 
REFERENCES comm_Items(SalePrice)     

ON DELETE CASCADE,

CONSTRAINT comm_order_shipped_chk CHECK (Shipped IN ('Y','N'))    
);

Answer 1

对customers和items的非主键列的引用确实会引发错误。

最重要的是，您不应该复制引用表中的信息。一个外键就足够了。

因此：

CREATE TABLE comm_Orders (
orderID NUMBER(10) NOT NULL,
OrderQty NUMBER(4) NOT NULL,
OrderDate DATE NOT NULL,
Shipped VARCHAR2(1),
ShippedDate DATE,    
custID NUMBER(6) NOT NULL,
itemID NUMBER(4) NOT NULL,    
CONSTRAINT comm_order_orderid_pk PRIMARY KEY (orderID),
CONSTRAINT comm_order_custid_fk  FOREIGN KEY (custID) REFERENCES comm_Customers(custID),    
CONSTRAINT comm_order_itemid_fk  FOREIGN KEY (itemID) REFERENCES comm_Items(itemID), 
CONSTRAINT comm_order_shipped_chk CHECK (Shipped IN ('Y','N'))
);

然后，每当需要从引用表中恢复信息时，都可以使用外键join。假设你想要客户的电话：

select o.*, c.phone
from comm_orders o
inner join comm_customers c on c.custid = o.custid