mysql - 想要保持MySQL中两个表之间的关系

我有两个表PRODUCT和DISCOUNT，它们之间存在多对多关系。维护此关系的表是PRODUCTHASDISCOUNT。如果要删除产品，我想从PRODUCTHASDISCOUNT删除记录，但是如果由于某种原因删除了DISCOUNT，则不希望删除记录。在这种情况下，应在删除DISCOUNT ID之前为产品指定其他折扣。所以我在外键上尝试了ON DELETE RESTRICT，但是那不起作用。我正在使用InnoDB，所以无法使用SET DEFAULT。有什么建议么？

CREATE TABLE PRODUCT (
id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
prodName VARCHAR(50) NOT NULL UNIQUE,
price DECIMAL(6,2),
availability BOOLEAN,
description VARCHAR(150) NOT NULL,
size VARCHAR(20),
weight VARCHAR(10),
catID INT UNSIGNED DEFAULT '1'
)ENGINE=InnoDB;

CREATE TABLE DISCOUNT (
id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
percentOff DECIMAL(4,4) NOT NULL DEFAULT "0.0"
)ENGINE=InnoDB;

CREATE TABLE PRODUCTHASDISCOUNT (
prodID INT UNSIGNED NOT NULL,
percentID INT UNSIGNED NOT NULL,
startDate TIMESTAMP NOT NULL DEFAULT NOW(),
endDate TIMESTAMP,
PRIMARY KEY (prodID, percentID),
FOREIGN KEY (prodID) REFERENCES PRODUCT(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (percentID) REFERENCES PERCENT(id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;

最佳答案

使用DISCOUNT(id)代替PERCENT(id)

`CREATE TABLE PRODUCTHASDISCOUNT (
prodID INT UNSIGNED NOT NULL,
percentID INT UNSIGNED NOT NULL,
startDate TIMESTAMP NOT NULL DEFAULT NOW(),
endDate TIMESTAMP,
PRIMARY KEY (prodID, percentID),
FOREIGN KEY (prodID) REFERENCES PRODUCT(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (percentID) REFERENCES DISCOUNT(id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;`

关于mysql - 想要保持MySQL中两个表之间的关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26825224/