Homework help (unique constraint)

Question

I have some trouble with homework I got that I need to understand.

I have 4 tables:

1: customer 2: order 3: orderedproduct 4. product .

They are linked by foreign keys.

I need to make a unique constraint so that a customer can only place 1 unique order per day. If the same customer wants to order more on the same day, it has to be written on the already existing order.

Tables:

Customer:

CREATE TABLE CUSTOMER 
(
    CUSTOMERNR INT NOT NULL,
    NAME VARCHAR(256), 
    CITY VARCHAR(256),
    PRIMARY KEY (CUSTOMERNR)
) ENGINE=INNODB;   

Order:

CREATE TABLE ORDER 
(
    ORDERNR INT NOT NULL,
    ORDERDATE DATETIME,
    PRIMARY KEY (ORDERNR)),
    FOREIGN KEY (CUSTOMERNR) REFERENCES CUSTOMER(CUSTOMERNR)
            ON DELETE CASCADE
) ENGINE=INNODB;

OrderedProduct:

CREATE TABLE ORDEREDPRODUCT 
(
    OPNR INT NOT NULL,
    AMOUNT INT,
    FOREIGN KEY (ORDERNR) REFERENCES ORDER(ORDERNR),
    FOREIGN KEY (PRODUCTCODE) REFERENCES PRODUCT (PRODUCTCODE)
            ON DELETE CASCADE,
    CHECK (AMOUNT => 0)
) ENGINE=INNODB;

Product:

CREATE TABLE PRODUCT 
(
    PRODUCTCODE INT NOT NULL,
    NAME VARCHAR(256),
    TYPE VARCHAR(256),
    PRICE FLOAT,
    STOCK INT,
    PRIMARY KEY (PRODUCTCODE),
    CHECK (AMOUNT => 0)
) ENGINE=INNODB;

Answer 1

alter table order add unique index(orderdate, COSTUMERNR);