CODEWITHSUNDEEP
mysqlsql
octavemirbeau
octavemirbeau

Reputation: 507

How to add multiple order rows to given orderid (primary key) in a table

I'm building a small webshop and I have the problem that I can't insert multiple order rows to a specific order due to the primary key constraint. How can I get around this? Out of convenience I would like to have the id autoincremented...

CREATE TABLE order (
    id INT NOT NULL AUTO_INCREMENT,
    number INT,
    productid VARCHAR(15),
    customerid INT,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted TIMESTAMP DEFAULT NULL,
    ordered TIMESTAMP DEFAULT NULL,
    sent TIMESTAMP DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (productid) REFERENCES product(produktid),
    FOREIGN KEY (customerid) REFERENCES customer(id)
) ENGINE INNODB CHARSET utf8 COLLATE utf8_swedish_ci;

Upvotes: 0

Views: 424

Answers (1)

Barmar
Barmar

Reputation: 781503

You don't put products in the order table. You create another table that has foreign keys to the order and product tables.

CREATE TABLE order_product (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productid VARCHAR(15),
    orderid INT,
    quantity INT,
    UNIQUE KEY (productid, orderid),
    FOREIGN KEY (productid) REFERENCES product(produktid),
    FOREIGN KEY (orderid) REFERENCES order(id)
);

Upvotes: 3

Related Questions