我在家庭作业方面遇到了一些麻烦,我需要理解。
我有4张桌子:
1: customer
2:order
3: orderedproduct
4. product
.
它们通过外键链接。
我需要设置一个唯一的约束,以便客户每天只能下 1 个唯一的订单。如果同一客户想在同一天订购更多,则必须将其写在已经存在的订单上。
表:
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;
alter table order add unique index(orderdate, COSTUMERNR);
如果能够将日期时间列拆分为日期列和时间列,则可以使用简单的约束:
CREATE TABLE ORDER (
ORDERNR INT NOT NULL,
ORDERDATE DATE,
ORDERTIME TIME,
...
CONSTRAINT [UniqueOrder] UNIQUE NONCLUSTERED
(
COSTUMERNR, ORDERDATE
)