时间戳

首页 > 解决方案 > 多个外键oracle

sql - 多个外键oracle

问题描述

在这个数据库中，我创建了 7 个表，但在最后 2 个表中，thebilling和product_billing表中，我需要添加外键，我使用了下面的方法，但是在执行时在每个表上都出现了两个不同的错误。

这是我的代码：

CREATE TABLE CUSTOMER(
    CustomerID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL,
    Address VARCHAR(50)NOT NULL,
    ContactNumber NUMBER NOT NULL,
    Email VARCHAR(50) NOT NULL,
    PRIMARY KEY(CustomerID));
    
CREATE TABLE EMPLOYEE(
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL,
    ContactNumber NUMBER NOT NULL,
    Position VARCHAR(50)NOT NULL,
    Address VARCHAR(50)NOT NULL,
    Email VARCHAR(50) NOT NULL,
    PRIMARY KEY(EmployeeID));
    
CREATE TABLE DELIVERY(
    DeliveryID INT NOT NULL,
    Description VARCHAR(50) NOT NULL,
    DespatchDate DATE,
    DeliveryDate DATE,
    PRIMARY KEY(DeliveryID));
    
CREATE TABLE RETURNS(
    ReturnID INT NOT NULL,
    ReturnDate DATE NOT NULL,
    Reason VARCHAR(50) NOT NULL,
    PRIMARY KEY(ReturnID));
    
CREATE TABLE PRODUCT(
    ProductID INT NOT NULL,
    Product VARCHAR(50) NOT NULL,
    Price VARCHAR(50) NOT NULL,
    QTY INT NOT NULL,
    PRIMARY KEY(ProductID));
    
CREATE TABLE BILLING(
    BillID INT NOT NULL,
    CONSTRAINT CustomerID FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID),
    BillDate DATE,
    CONSTRAINT EmployeeID FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    PRIMARY KEY(BillID));
    
CREATE TABLE PRODUCT_BILLING(
    CONSTRAINT DeliveryID FOREIGN KEY (DeliveryID) REFERENCES DELIVERY(DeliveryID),
    CONSTRAINT ReturnID FOREIGN KEY (ReturnID) REFERENCES RETURNS(ReturnID),
    CONSTRAINT ProductID FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID),
    CONSTRAINT BillID FOREIGN KEY (BillID) REFERENCES BILLING(BillID));

这些是错误：

Error starting at line : 40 in command -
CREATE TABLE BILLING(
    BillID INT NOT NULL,
    CONSTRAINT CustomerID FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID),
    BillDate DATE,
    CONSTRAINT EmployeeID FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    PRIMARY KEY(BillID))
Error report -
ORA-00904: "CUSTOMERID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


Error starting at line : 47 in command -
CREATE TABLE PRODUCT_BILLING(
    CONSTRAINT DeliveryID FOREIGN KEY (DeliveryID) REFERENCES DELIVERY(DeliveryID),
    CONSTRAINT ReturnID FOREIGN KEY (ReturnID) REFERENCES RETURNS(ReturnID),
    CONSTRAINT ProductID FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID),
    CONSTRAINT BillID FOREIGN KEY (BillID) REFERENCES BILLING(BillID))
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

标签： sqloracle

解决方案

请使用以下语法创建外键引用，

CREATE TABLE BILLING(
    BillID INT NOT NULL,
    CustomerID INT REFERENCES CUSTOMER(CustomerID),
    BillDate DATE,
    EmployeeID INT REFERENCES EMPLOYEE(EmployeeID),
    PRIMARY KEY(BillID));
    
CREATE TABLE PRODUCT_BILLING(
     DeliveryID INT REFERENCES DELIVERY(DeliveryID),
     ReturnID INT REFERENCES RETURNS(ReturnID),
     ProductID INT REFERENCES PRODUCT(ProductID),
     BillID INT REFERENCES BILLING(BillID));

推荐阅读