Oracle Missing expression

Question

I only have this error now.. I cant seem to see where it is too long (ERROR at bottom of page)

CREATE TABLE Menu_Item_Ingredient
(
  Menu_Item_Number Number(5,0)CONSTRAINT NN_MenuItemIngredient_MenuItemNumber Not null,
    CONSTRAINT FK_MenuItemIngredient_MenuItemNumber Foreign Key(Menu_Item_Number) References Bill_Item(Menu_item_Number),
  Ingredient_Number Number(5,0) CONSTRAINT NN_MenuItemIngredient_IngredientNumber Not null,
    CONSTRAINT FK_MenuItemIngredient_IngredientNumber Foreign Key(Ingredient_Number) References Ingredient(Ingredient_Number),
  Quantity_Needed Number(5,2) DEFAULT 0 CONSTRAINT NN_MenuItemIngredient_QuantityNeeded Not null
    CONSTRAINT CK_MenuItemIngredient_QuantityNeeded CHECK(Quantity_Needed >= 0),
    CONSTRAINT PK_BillItem_Ingredient Primary key(Menu_Item_Number,Ingredient_Number)
)

Error report - SQL Error: ORA-00972: identifier is too long 00972. 00000 - "identifier is too long" *Cause: An identifier with more than 30 characters was specified. *Action: Specify at most 30 characters.

Answer 1

Do something below. In your query, a comma is missing when you are declaring discount column in bill_item table.

CREATE TABLE Bill_Item3
(
    Bill_Number      NUMBER(6,0) CONSTRAINT NN_BillItem_BillNumber NOT NULL,
    Menu_Item_Number NUMBER(5,0) CONSTRAINT NN_BillItem_MenuItemNumber NOT NULL,
    Discount         NUMBER(5,2) CONSTRAINT N_BillItem_Discount NULL,
    --colum Discount format '%'00.00,
    Quaintity_Sold   NUMBER(3,0)CONSTRAINT NN_BillItem_QuaintitySold NOT NULL,
    Selling_Price    NUMBER(6,2) default 0 CONSTRAINT NN_BillItem_SellingPrice NOT NULL,
    CONSTRAINT CK_BillItem_SellingPrice CHECK (Selling_Price >= 0  ), 
    CONSTRAINT PK_Bill_MenuItem PRIMARY KEY (Bill_Number,Menu_Item_Number),
    CONSTRAINT FK_BillItem_Bill FOREIGN KEY (Bill_Number) REFERENCES bill (Bill_Number),
    CONSTRAINT FK_BillItem_MenuItemNumber FOREIGN KEY (Menu_Item_Number) REFERENCES Menu_Item_Ingredient(Menu_Item_Number),
    CONSTRAINT CK_BillItem_Discount CHECK (Discount BETWEEN 0 AND 100)
);

Answer 2

Your problem is with the constraints,

1. A foreign key constraint will be used to give reference to a column in another table, in your queries you have given constraints like below -

   "CONSTRAINT FK_BillItem_Bill Foreign key (Bill_Number) References Primary(Bill_Number), what are you trying to do with this?

   The syntax you need to use should be -

   CONSTRAINT constraint_name FOREIGN KEY (column_in_current_table) REFERENCES Other_Table_Name(column_name_you_want_to_refer_in_other_table)
   

2. As far as I've seen, this will not work, you cannot use DEFAULT in a check constraint.

   constraint CK_BillItem_SellingPrice check (Selling_Price >= 0 OR DEFAULT = 0)
   

   If you want to enforce a DEFAULT constraint, you have to use something as below -

   CREATE TABLE Persons (
       ID int NOT NULL,
       LastName varchar(255) NOT NULL,
       FirstName varchar(255),
       Age int,
       City varchar(255) DEFAULT 'Sandnes'
   );
   

   Your final table can be as follows -

   CREATE TABLE Bill_Item
   (
     Bill_Number Number(6,0) constraint NN_BillItem_BillNumber not null,
     --CONSTRAINT FK_BillItem_Bill Foreign key (Bill_Number) References Primary(Bill_Number), 
     Menu_Item_Number Number(5,0) CONSTRAINT NN_BillItem_MenuItemNumber Not null,
     --CONSTRAINT FK_BillItem_MenuItemNumber Foreign Key (Menu_Item_Number) References Primary(Menu_Item_Number),
     Discount Number(5,2) CONSTRAINT N_BillItem_Discount Null
     CONSTRAINT CK_BillItem_Discount Check (Discount BETWEEN 0 and 100),
     --colum Discount format '%'00.00,
     Quaintity_Sold Number(3,0)CONSTRAINT NN_BillItem_QuaintitySold not null,
     Selling_Price Number(6,2) DEFAULT 0.0 CONSTRAINT NN_BillItem_SellingPrice not null, -- You can remove "DEFAULT 0.0" if you do not want to default your Selling_Price to 0.0
     constraint CK_BillItem_SellingPrice check (Selling_Price >= 0),
     CONSTRAINT PK_Bill_MenuItem Primary key (Bill_Number,Menu_Item_Number)
   );
   

3. All of your other tables also have the same issue, remove the DEFAULT from check constraint as I did in the above example, and specify "Default" constraint at the column definition itself.

4. Also, create the table which will be referenced in other tables first, for example, your second table "Bill" has a foreign key constraint which is referencing to "Waiter" table, so you have to create "Waiter" table first.

If you are still having concerns let me know.