Search code examples
mysqlsqlauto-incrementshopping-cart

sql cannot insert same customer ID into shopping cart


Im asking for direction. I have a .sql file of tables - customer, product and shopping cart. The shopping cart has the customerID and productID both auto incremented and both as the primary keys. I INSERTed a record into the shopping cart for a specific customerID, specific productID with a quantity of 1. I INSERTed another record into the shopping cart for the same customer.
When trying to import into phpMyAdmin, I get an error: #1062 - Duplicate entry '0-0' for key 'PRIMARY' How do I add more than one record to the shopping cart with the same customerID?

CREATE TABLE Shopping_Cart(
  CustID INTEGER UNSIGNED NOT NULL,
  ProdID INTEGER UNSIGNED NOT NULL,
  Quantity INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(CustID, ProdID));

INSERT INTO Shopping_Cart VALUES("BostockG12345A", "DULL12275226", 2);
INSERT INTO Shopping_Cart VALUES("BostockG12345A", "jPET1224108", 1); 

Thank you all. I will change it from an integer to VARCHAR when I get home. I created the table before I assigned IDs. But that leads me to another question.
For the shopping cart I made up the CustID and ProdID even though they are a auto_increment in the Customer table and Product table. Im getting a little confused with which comes first, the chicken or the egg (the auto_increments CustID (and ProdID) or the INSERTs CustID (or ProdID). How do I know what the auto_increment ID number is for the shopping carts INSERT if its all in the same file? Should I do a query for the ID of the customer whose name is Bostock and the ID of that specific product, then do the INSERT?


Solution

  • CustID INTEGER UNSIGNED NOT NULL, --> Integer
      ProdID INTEGER UNSIGNED NOT NULL, -->Integer
    
    INSERT INTO Shopping_Cart VALUES("BostockG12345A", "DULL12275226", 2);
    INSERT INTO Shopping_Cart VALUES("BostockG12345A", "jPET1224108", 1);
    
       Wrong May be :  But you are inserting string data in place of integer