Oracle建表遇ORA-00904/ORA-00942错误，请求协助解决

问题概述 #

创建关联业务表时，Suppliers、Payments、Shippers、Customers、Category表创建成功，但Products、OrderDetails、Orders表触发以下错误：

• Products和OrderDetails报ORA-00904: 无效标识符
• Orders报ORA-00942: 表或视图不存在

错误原因分析 #

1. 保留关键字冲突：Oracle中Size、TimeStamp是系统保留关键字，直接作为列名会触发无效标识符错误。
2. 外键逻辑倒置：Orders表试图将自身主键OrderID作为外键引用OrderDetails的OrderID，违背实际关联逻辑（应为OrderDetails引用Orders的OrderID），且此时OrderDetails未创建成功，导致表不存在报错。
3. 字段类型错误：Orders表的OrderDate被定义为NUMBER类型，实际应使用DATE类型存储日期数据。

修正后的建表SQL #

Products表（解决关键字冲突） #

CREATE TABLE Products 
( 
    ProductID NUMBER NOT NULL PRIMARY KEY,
    SKU VARCHAR2(255) NOT NULL,
    IDSKU VARCHAR2(255) NOT NULL,
    VendorProductID NUMBER NOT NULL,
    ProductName VARCHAR2(255) NOT NULL,
    ProductDescription VARCHAR2(255) NOT NULL,
    SupplierID NUMBER NOT NULL,
    CategoryID NUMBER NOT NULL,
    QuantityPerUnit VARCHAR2(255) NOT NULL,
    UnitPrice NUMBER NOT NULL,
    MSRP NUMBER NOT NULL,
    AvailableSize VARCHAR2(255),
    AvailableColors VARCHAR2(255) NOT NULL,
    "Size" NUMBER NOT NULL, -- 关键字用双引号包裹避免冲突
    Color VARCHAR2(255) NOT NULL,
    Discount VARCHAR2(255) NOT NULL,
    UnitWeight VARCHAR2(255) NOT NULL,
    UnitsInStock VARCHAR2(255) NOT NULL,
    UnitsInOrder VARCHAR2(255) NOT NULL,
    ReorderLevel VARCHAR2(255) NOT NULL,
    ProductAvailable NUMBER NOT NULL,
    DiscountAvailable NUMBER NOT NULL,
    CurrentOrder VARCHAR2(255) NOT NULL,
    Picture VARCHAR2(255) NOT NULL,
    Ranking NUMBER NOT NULL,
    Note VARCHAR2(255),
    CONSTRAINT fksup FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
    CONSTRAINT catsup FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);

Orders表（修正类型、关键字+调整外键逻辑） #

CREATE TABLE Orders 
(  
    OrderID NUMBER NOT NULL PRIMARY KEY,
    CustomerID NUMBER NOT NULL,
    OrderNumber NUMBER NOT NULL,
    PaymentID NUMBER NOT NULL,
    OrderDate DATE NOT NULL, -- 修正为DATE类型
    ShipDate DATE NOT NULL,
    RequiredDate DATE NOT NULL,
    ShipperID NUMBER NOT NULL,
    Freight VARCHAR2(255) NOT NULL,
    SalesTax VARCHAR2(255),
    "TimeStamp" VARCHAR2(255), -- 关键字用双引号包裹避免冲突
    TransactStatus NUMBER(1,0) NOT NULL,
    ErrLoc VARCHAR2(255),
    ErrMsg VARCHAR2(255),
    Fulfilled VARCHAR2(255),
    Deleted NUMBER(1,0) NOT NULL,
    Paid VARCHAR2(255) NOT NULL,
    PaymentDate DATE NOT NULL,
    CONSTRAINT odemedetay FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
    CONSTRAINT shipperdetay FOREIGN KEY (ShipperID) REFERENCES Shippers(ShipperID),
    CONSTRAINT musteridetay FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

OrderDetails表（解决关键字+修正外键引用） #

CREATE TABLE OrderDetails
( 
    OrderDetailID NUMBER NOT NULL PRIMARY KEY,
    OrderID NUMBER NOT NULL,
    ProductID NUMBER NOT NULL,
    OrderNumber NUMBER NOT NULL,
    Price NUMBER NOT NULL,
    Quantity NUMBER NOT NULL,
    Discount NUMBER NOT NULL,
    Total NUMBER NOT NULL,
    IDSKU NUMBER NOT NULL,
    "Size" NUMBER NOT NULL, -- 关键字用双引号包裹避免冲突
    Color VARCHAR2(255) NOT NULL,
    FullFilled VARCHAR2(255),
    ShipDate DATE NOT NULL,
    BillDate DATE NOT NULL,
    CONSTRAINT uruncekme FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    CONSTRAINT order_fk FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

执行顺序要求 #

必须按以下顺序执行建表语句，确保外键引用的表已存在：

1. Suppliers、Payments、Shippers、Customers、Category（已成功创建）
2. Products
3. Orders
4. OrderDetails（依赖Products和Orders）

内容的提问来源于stack exchange，提问作者MEMORY BREAK