CODEWITHSUNDEEP
mysqlsqlconstraintsunique
DaViDa
DaViDa

Reputation: 641

Homework help (unique constraint)

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;

Upvotes: 0

Views: 88

Answers (2)

Josteinr
Josteinr

Reputation: 16

If you're able to split the datetime column into a date column and a time column, you can use a simple constraint:

CREATE TABLE ORDER (
   ORDERNR INT NOT NULL,
   ORDERDATE DATE,
   ORDERTIME TIME,
    ...
CONSTRAINT [UniqueOrder] UNIQUE NONCLUSTERED
(
   COSTUMERNR, ORDERDATE
)

Upvotes: 0

AnthonyBlake
AnthonyBlake

Reputation: 2354

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

Upvotes: 1

Related Questions