M:N Relation SQL Error 00904 Invalid Identifier when Create Table

Question

I have looked up how to create tables of M:N Relations.

Here is my SQL Code:

CREATE TABLE "product"(
    "ProductID" INT PRIMARY KEY,
    "ProductName" VARCHAR(25) NOT NULL,
    "Price" NUMBER NOT NULL,
    "Description" VARCHAR(25),
    "Quantity" INT NOT NULL
);

CREATE TABLE "transaction"(
    "TransactionID" INT PRIMARY KEY,
    "Date" INT NOT NULL
);


CREATE TABLE "trade"(
    "ProdID" INT REFERENCES "product"("ProductID"),
    "TransID" INT REFERENCES "transaction"("TransactionID"),
    "QuantityPurchased" INT NOT NULL,
    PRIMARY KEY (TransID, ProdID)
);

When I run this I get: Error report - ORA-00904: "TRANSID": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

Is there anything wrong with my code?

If you use double-quoted mixed-case identifiers you must use them **all the time**. The simplest solution is never to use double-quotes when creating objects. Then you can reference them without needing to worry about case. — APC, Oct 01 '18 at 06:16

score 2 · Answer 1 · answered Sep 30 '18 at 21:57

Using double quotes makes an identifier (in this case, a column name) case-sensitive. Stick to consistent quoting, and you should be OK:

CREATE TABLE "trade"(
    "ProdID" INT REFERENCES "product"("ProductID"),
    "TransID" INT REFERENCES "transaction"("TransactionID"),
    "QuantityPurchased" INT NOT NULL,
    PRIMARY KEY ("TransID", "ProdID")
    -- Here -----^-------^--^------^
);

score 0 · Answer 2 · answered Oct 01 '18 at 00:19

A recommendation: don't use quoted identifiers. It just makes everyone's job harder. You can still use transaction or Transaction rather than TRANSACTION because the database will translate the unquoted names the default of upper case. Double-quoting names when defining something makes everyone else use double-quotes when referencing that particular item, makes code harder to read, and opens the door to quoting mismatches, such as occurred here. In addition, some workplaces have coding standards which do not allow quoted identifiers. I suggest:

CREATE TABLE product(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(25) NOT NULL,
    Price NUMBER NOT NULL,
    Description VARCHAR(25),
    Quantity INT NOT NULL
);

CREATE TABLE transaction(
    TransactionID INT PRIMARY KEY,
    transaction_Date INT NOT NULL
);

CREATE TABLE trade(
    ProdID INT REFERENCES product(ProductID),
    TransID INT REFERENCES transaction(TransactionID),
    QuantityPurchased INT NOT NULL,
    PRIMARY KEY (TransID, ProdID)
);

M:N Relation SQL Error 00904 Invalid Identifier when Create Table

2 Answers2