Simple database with 3 tables and “no matching unique or primary key for this column”

I have three tables, two are created independently, and the third one is created to include some inputs from the first two. First two tables have no problems, however, when I try to create the third one, I get an error:

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

The thing is, I created the third table by copy/pasting the column names/definitions right from the first two tables, yet I still get this ridiculous error message. Now I wonder if the order of the columns and the especially the order of constrains is important.

The tables:

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)    
);

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)
);

comm_Orders which gives the error

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

The references to non-primary key columns of customers and items do raise the error.

Bottom-line, you should not be duplicating the information from the referential tables. A single foreign key is sufficient.

So:

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'))
);

Then, whenever you need to recover an information from a referential table, you join it using the foreign key. Say you want the phone of a customer:

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

Advertisement

Answer