CODEWITHSUNDEEP
sqloracle-databaseforeign-keysoracle12ccreate-table
Evan Walter
Evan Walter

Reputation: 9

SQL Error: ORA-02267 column type incompatible with referenced column type

Source code for oracle 12c

CREATE TABLE CUSTOMER (
CustomerID      Char(20)    NOT NULL,
CustomerFirstName   Char(20) NOT NULL,
CustomerLastName    Char(25) NOT NULL,
CustomerAddress Char(45) NOT NULL,
CustomerEmail       Char(100) NOT NULL,
CONSTRAINT      Customer_PK Primary Key(CustomerID)

CREATE TABLE EMPLOYEE ( 
EmployeeID      VarChar(10) NOT NULL, 
EmployeeFName   VarChar(20)     NOT NULL, 
EmployeeRole        VarChar(30)     NOT NULL, CONSTRAINT        
Employee_PK Primary Key(EmployeeID) 

CREATE TABLE PRODUCT ( 
ProductID       VarChar(10) NOT NULL, 
ProductName     VarChar(20)     NOT NULL, 
ProductType     VarChar(20)     NOT NULL, 
ProductPrice        Number(8,2) NOT NULL, 
CONSTRAINT      Product_PK Primary Key(ProductID) 

CREATE TABLE CUSTORDER ( 
CustOrderID         VarChar(10) NOT NULL, 
CustOrderDate       Date    NOT NULL, 
CustShippingStatus      VarChar(20)     NOT NULL,
SupplierID      VarChar(20)     NOT NULL, 
CONSTRAINT      CustOrder_PK Primary Key (CustOrderID),
CONSTRAINT          CustOrder_FK FOREIGN KEY(SupplierID)
REFERENCES SUPPLIER(SupplierID) 

CREATE TABLE SUPPLIER ( SupplierID      Char(10)    NOT NULL, 
SupplierName        Char(20)    NOT NULL, 
SupplierAddress Char(45)    NOT NULL,
CONSTRAINT      Supplier_PK Primary Key (SupplierID)

CREATE TABLE INVOICE (
InvoiceID       Char(20)    NOT NULL,
TotalItems      Int         NOT NULL,
TotalCost       Number(8,2) NOT NULL,
SalesDate       Date        NOT NULL,
PaymentType     VarChar(10) NOT NULL,
ProductID       VarChar(10) NOT NULL,
EmployeeID      VarChar(10) NOT NULL,
CustomerID      Char(20)    NOT NULL,
SupplierID      Char(10)    NOT NULL, 
CONSTRAINT      Invoice_PK Primary Key(InvoiceID),
CONSTRAINT      Invoice_Product_FK Foreign Key(ProductID)
REFERENCES  PRODUCT(ProductID),
CONSTRAINT      Invoice_Employee_FK Foreign Key(EmployeeID)
REFERENCES  EMPLOYEE(EmployeeID),
CONSTRAINT      Invoice_Customer_FK Foreign Key(CustomerID)
REFERENCES  CUSTOMER(CustomerID),
CONSTRAINT      Invoice_Supplier_FK Foreign Key(SupplierID)
REFERENCES  SUPPLIER(SupplierID)

Upvotes: 0

Views: 1483

Answers (1)

GMB
GMB

Reputation: 222482

You need to align the datatype and length of the referencing column of each foreign key with the column it references in the source table.

In your code, SUPPLIER(SupplierID) is declared as Char(20). On the other hand, referencing column INVOICE(SupplierID) is Char(10) and CUSTORDER(SupplierID) is VarChar(20). If you make these two columns Char(20), you code just works.

Aside: the referenced table must exist at the time when the referencing table is created - your code createst CUSTORDER before SUPPLIER. However I assume that's a typo when writing the question, otherwise you would be getting a different error: ORA-00942: table or view does not exist.

You also have a missing right parentheses at the end of each and every create table statement: I would consider these typos too.

Demo on DB Fiddle

Upvotes: 1

Related Questions