mysql - DELETE 语句与外键 REFERENCE 冲突

Question

我试图弄清楚如何从存在外键约束的表中删除一行。它应该根据给定的删除一行Item_Number

这些表格是：

CREATE TABLE MENU(
Item_Number int NOT NULL AUTO_INCREMENT,
Store_Number INT(10),
Price FLOAT(10,2),
Item_Description CHAR(35),
PRIMARY KEY(Item_Number)
);

CREATE TABLE ORDER_MENU(
Order_ID INT(10),
Item_Number int,
PRIMARY KEY(Order_ID, Item_Number),
FOREIGN KEY(Order_ID) REFERENCES ORDERS(Order_ID),
FOREIGN KEY(Item_Number) REFERENCES MENU(Item_Number)
);

我目前的陈述没有删除任何东西！我尝试切换表格的顺序，但什么也没有！

尝试声明 1：

sql ="ALTER TABLE ORDER_MENU WITH CHECK  ADD CONSTRAINT Item_Number FOREIGN KEY(<Order_ID, Item_Number>) REFERENCES MENU (Item_Number, Store_Number, Price, Item_Description) ON DELETE CASCADE;";

尝试的陈述 2：

sql = "BEGIN TRANSACTION SET XACT_ABORT ON DELETE ORDER_MENU WHERE Item_Number IN (<select Item_Number from [ORDER_MENU].[MENU] AS Item_Number WHERE Item_Number.ObjectID = MENU.Item_Number)>) DELETE Item_Number WHERE  Item_Number= : Item_Number COMMIT TRANSACTION;";

标签： mysql