-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)
);
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)
| improve this answer | |

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