-2

The error is in the Items table. What could be the reason, how to fix it?

CREATE TABLE Users
(

    userid NUMBER(6) NOT NULL,
    username VARCHAR2(150) NOT NULL UNIQUE,
    email VARCHAR2(200) NOT NULL UNIQUE,
    password VARCHAR2(255),
    role VARCHAR2(20),
    CONSTRAINT pk_users PRIMARY KEY (userid)
);

CREATE TABLE Menus
(   

    menuid NUMBER(6) NOT NULL,
    title VARCHAR2(255) NOT NULL UNIQUE,
    menutype VARCHAR2(45),
    description VARCHAR2(1000),
    status NUMBER(1) NOT NULL ,
    image VARCHAR2(255),
    user_id NUMBER(6) NOT NULL,
    CONSTRAINT pk_menus PRIMARY KEY (menuid, user_id),
    CONSTRAINT fk_menus_to_users FOREIGN KEY (user_id) references Users(userid)
);

CREATE TABLE Items
(

    itemid NUMBER(6) NOT NULL,
    title VARCHAR2(255) NOT NULL UNIQUE,
    description VARCHAR2(1000),
    status NUMBER(1) NOT NULL,
    image VARCHAR2(255),
    price NUMBER(10, 2),
    menu_id NUMBER(6) NOT NULL,
    user_id NUMBER(6) NOT NULL,
    CONSTRAINT pk_items PRIMARY KEY (itemid),
    CONSTRAINT fk_items_to_users FOREIGN KEY (user_id) references Users(userid),
    CONSTRAINT fk_items_to_menus FOREIGN KEY (menu_id) references Menus(menuid)
);

closed as off-topic by Philᵀᴹ, mustaccio, Max Vernon, RDFozz, Erik Darling Jun 28 '18 at 21:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, mustaccio, Max Vernon, RDFozz, Erik Darling
If this question can be reworded to fit the rules in the help center, please edit the question.

3

Remember that primary key of the Menus table is menuid + user_id. Thus, you can not reference only menuid.

The solution is:

Replace:

CONSTRAINT fk_items_to_users FOREIGN KEY (user_id) references Users(userid),
CONSTRAINT fk_items_to_menus FOREIGN KEY (menu_id) references Menus(menuid)

with:

CONSTRAINT fk_items_to_menus FOREIGN KEY (menu_id,user_id) references Menus(menuid,user_id)

Not the answer you're looking for? Browse other questions tagged or ask your own question.