Creating a cart in MYSQL Workbench-CodePudding

I'm creating a cart where users can have multiple items in their cart, but not duplicates, and multiple users can have have the same listing in their cart. I'm not sure how to get it to have prevent a user from adding the same item to their cart multiple times.

CREATE TABLE CART (
    USERID INT NOT NULL, 
    LISTINGID INT NOT NULL,
    FOREIGN KEY(USERID) REFERENCES USERS(ID),
    FOREIGN KEY(LISTINGID) REFERENCES LISTING(ID)
);

CREATE PROCEDURE ADDCART(
    IN displayname__ VARCHAR(32),
    IN listingID__ int
)
BEGIN
    INSERT INTO CART
        VALUES((SELECT ID FROM USERS WHERE displayname__ = USERS.DISPLAYNAME), listingID__);
END //

CodePudding user response：

how to prevent a user from adding the same item to their cart multiple times

You do that simply by defining a unique key on the cart table. Since you don't seem to have a primary key defined on that table, it would make sense to use it as such:

CREATE TABLE CART (
    USERID INT NOT NULL, 
    LISTINGID INT NOT NULL,
    PRIMARY KEY (USERID, LISTINGID),
    FOREIGN KEY(USERID) REFERENCES USERS(ID),
    FOREIGN KEY(LISTINGID) REFERENCES LISTING(ID)
);

This enforces the proper integrity check on your data, and there is probably no need for a stored procedure. If an offending insert is attempted, the relevant error is raised directly by by the database, and can then be handled in your application.

insert into USERS   (ID) values(1), (2);
insert into LISTING (ID) values(1), (2);

insert into CART (USERID, LISTINGID) values (1, 1);
insert into CART (USERID, LISTINGID) values (1, 2);
insert into CART (USERID, LISTINGID) values (2, 1);
-- ok

insert into CART (USERID, LISTINGID) values (1, 1); 
-- ERROR: Duplicate entry '1-1' for key 'CART.PRIMARY'

Demo on DB Fiddle