CODEWITHSUNDEEP
sqloracle-database
user13791122
user13791122

Reputation:

Multiple foreign keys oracle

In this database I have created 7 tables but in my last 2 tables, the billing and the product_billing table, I need to add foreign keys, I have used the method below but getting two different errors on each table when executing.

This is my code:

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

Those are the errors:

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:

Upvotes: 0

Views: 89

Answers (1)

Jim Macaulay
Jim Macaulay

Reputation: 5141

Please use below syntax to create foreign key references,

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

Upvotes: 2

Related Questions