ORACLE - Creating multiple tables with same column names

There are three issues in your code:

1. If you are using the oracle12c or higher then object names of 128 character long are allowed. If you are on oracle 11g or below then only 30 characters are allowed. You should consider changing the name of the constraint: ORDERDETAILS_ORDERID_PRODUCTID_PK to make it less then 30 characters.

2. You have used date data type as DATE(7) but in oracle date data type do not have any length parameter. So use DATE without data length i.e. (7)

3. Create SUPPLIERS table before PRODUCTS table as PRODUCTS table refers to the SUPPLIERS table.

See db<>fiddle demo

Cheers!!

J

Execute this below: It fixes the date datatype and the order between supplier and products and finally the length of constraint ORDERDETAILS_ORDERID_PRODUCTID_PK

    CREATE TABLE CATEGORIES 
(

    CATEGORYID NUMBER(8,0),
    CATEGORYNAME VARCHAR2(15) NOT NULL,
    CATEGORYCODE NUMBER(6,0),
    DESCRIPTION VARCHAR2(300),
    CONSTRAINT CATEGORIES_CATEGORYID_PK PRIMARY KEY (CATEGORYID)
);


CREATE TABLE CUSTOMERS 
(
    CUSTOMERID CHAR(5),
    COMPANYNAME VARCHAR2(40) NOT NULL,
    CONTACTNAME VARCHAR2(30),
    CONTACTTITLE VARCHAR2(30),
    ADDRESS VARCHAR2(30),
    CITY VARCHAR2(15),
    REGION VARCHAR2(15),
    POSTALCODE VARCHAR2(10),
    COUNTRY VARCHAR2(15),
    PHONE VARCHAR2(24),
    FAX VARCHAR2(24),
    EMAIL VARCHAR2(50),
    CONSTRAINT CUSTOMERS_CUSTOMERID_PK PRIMARY KEY(CUSTOMERID) 
);


CREATE TABLE EMPLOYEES
(
    EMPLOYEEID NUMBER(8,0),
    LASTNAME VARCHAR2(20) NOT NULL,
    FIRSTNAME VARCHAR2(10) NOT NULL,
    TITLE VARCHAR2(30),
    TITLEOFCOURTESY VARCHAR2(25),
    BIRTHDATE DATE,
    HIREDATE DATE,
    ADDRESS VARCHAR2(60),
    CITY VARCHAR2(15),
    REGION VARCHAR2(15),
    POSTALCODE VARCHAR2(10),
    COUNTRY VARCHAR2(15),
    HOMEPHONE VARCHAR2(24),
    EXTENSION VARCHAR2(4),
    NOTES VARCHAR2(600),
    REPORTSTO NUMBER(8,0),
    SIN CHAR(9),
    CONSTRAINT EMLOYEES_EMPLOYEEID_PK PRIMARY KEY(EMPLOYEEID)


);


CREATE TABLE ORDERDETAILS
(
    ORDERID NUMBER(8,0),
    PRODUCTID NUMBER(8,0),
    UNITPRICE NUMBER(8,2) NOT NULL,
    QUANTITY NUMBER(6,0) NOT NULL,
    DISCOUNT NUMBER(2,2) NOT NULL,
CONSTRAINT ORDERDETLS_ORDRID_PRODID_PK PRIMARY KEY (ORDERID, PRODUCTID)

);


CREATE TABLE ORDERS
(
    ORDERID NUMBER(8,0),
    CUSTOMERID CHAR(5),
    EMPLOYEEID NUMBER(22),
    TERRITORYID VARCHAR2(20),
    ORDERDATE DATE,
    REQUIREDATE DATE,
    SHIPPEDDATE DATE,
    SHIPVIA NUMBER(8,0),
    FREIGHT NUMBER(8,2),
    SHIPNAME VARCHAR2(40),
    SHIPADDRESS VARCHAR2(60),
    SHIPCITY VARCHAR2(15),
    SHIPREGION VARCHAR2(15),
    SHIPPOSTALCODE VARCHAR2(10),
    SHIPCOUNTRY VARCHAR2(15),
    CONSTRAINT ORDERS_ORDERID_PK PRIMARY KEY(ORDERID),
    CONSTRAINT ORDERS_CUSTOMERID_FK FOREIGN KEY (CUSTOMERID) 
    REFERENCES CUSTOMERS(CUSTOMERID),
    CONSTRAINT ORDERS_EMPLOYEEID_FK FOREIGN KEY (EMPLOYEEID) 
    REFERENCES EMPLOYEES(EMPLOYEEID)
);


CREATE TABLE SUPPLIERS
(
    SUPPLIERID NUMBER(8,0),
    COMPANYNAME VARCHAR2(40) NOT NULL,
    CONTACTNAME VARCHAR2(30),
    CONTACTTITLE VARCHAR2(30),
    ADDRESS VARCHAR2(60),
    CITY VARCHAR2(15),
    REGION VARCHAR2(15),
    POSTALCODE VARCHAR2(10),
    COUNTRY VARCHAR2(15),
    PHONE VARCHAR2(24),
    FAX VARCHAR2(24),
    HOMEPAGE VARCHAR(200),
    CONSTRAINT SUPPLIERS_SUPPLIERID_PK PRIMARY KEY(SUPPLIERID)

);


CREATE TABLE PRODUCTS
(
    PRODUCTID NUMBER(8,0),
    PRODUCTNAME VARCHAR2(4) NOT NULL,
    SUPPLIERID NUMBER(8,0),
    CATEGORYID NUMBER(8,0),
    QUANTITYPERUNIT VARCHAR2(20),
    UNITPRICE NUMBER(8,2),
    UNITSINSTOCK NUMBER(6,0),
    UNITSONORDER NUMBER(6,0),
    REORDERLEVEL NUMBER(6,0),
    DISCONTINUED NUMBER(1,0) NOT NULL,
    CONSTRAINT PRODUCTS_CATEGORYID_FK FOREIGN KEY (CATEGORYID)
    REFERENCES CATEGORIES(CATEGORYID),
    CONSTRAINT PRODUCTS_SUPPLIERID_FK FOREIGN KEY (SUPPLIERID)
    REFERENCES SUPPLIERS(SUPPLIERID)

);


CREATE TABLE SHIPPERS
(
    SHIPPERID NUMBER(8,0),
    COMPANYNAME VARCHAR2(40) NOT NULL,
    PHONE VARCHAR2(24),
    CONSTRAINT SHIPPERS_SHIPPERID_PK PRIMARY KEY(SHIPPERID)


);