问题描述
我有3个表:lt_hdefaults
,lt_hperiods
和lt_hrules
.在lt_hdefaults
中,特定年份的属性有一行.在lt_hperiods
表中,可能有多个期间,称为季节.在lt_hrules
表中,每个期间可能有多个规则.现在,我无法进行的工作:当用户从lt_hdefaults
删除记录时,应该从lt_hperiods
和lt_hrules
表中删除与删除的记录相关的其他数据.我正在尝试通过使用
I have got 3 tables: lt_hdefaults
, lt_hperiods
and lt_hrules
. In lt_hdefaults
, there is one row for a property for a particular year. In the lt_hperiods
table, there could be more than one period, call them seasons. In the lt_hrules
table, there could be more than one rule for each period. Now, what I could not make work: when the user deletes a record from lt_hdefaults
, other data related to the deleted record should be removed from lt_hperiods
and lt_hrules
table. I am trying to achieve this by using
FOREIGN KEY (lt_year,lt_id) REFERENCES lt_hdefaults(lt_year,lt_id) ON DELETE CASCADE
但是,它不起作用.我知道它看起来很长,但不是很复杂.如果有人有任何想法,我将不胜感激.我知道如何使用mysql,但是我不是这方面的专家.非常感谢.
However, it does not work. I know it looks long but it's not very complicated. If anyone has any idea, I would appreciate it. I know how to use mysql however I am not an expert on that. Thanks very much.
以下示例:
CREATE TABLE IF NOT EXISTS lt_hdefaults (
lt_year year(4) NOT NULL DEFAULT '0000',
lt_id int(255) NOT NULL DEFAULT '0',
period_name varchar(45) NOT NULL DEFAULT 'Default',
min_stay int(10) NOT NULL DEFAULT '1',
max_stay int(10) NOT NULL,
weekly_rate float(10,2) NOT NULL,
nightly_rate float(10,2) NOT NULL,
PRIMARY KEY (lt_year,lt_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS lt_hperiods (
period_id int(255) NOT NULL ,
lt_id int(255) NOT NULL,
lt_year year(4) NOT NULL DEFAULT '0000',
period_name varchar(45) NOT NULL,
min_stay int(10) NOT NULL,
max_stay int(10) NOT NULL,
fromDate date NOT NULL,
toDate date NOT NULL,
weekly_rate float(10,2) DEFAULT NULL,
nightly_rate float(10,2) NOT NULL,
arriveDepartDays varchar(150) DEFAULT 'sunday,monday,tuesday,wednesday,thursday,friday,saturday',
noArriveDepartDays varchar(150) DEFAULT NULL,
PRIMARY KEY (period_id),
FOREIGN KEY (lt_year,lt_id) REFERENCES lt_hdefaults(lt_year,lt_id) ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=107 ;
CREATE TABLE IF NOT EXISTS lt_hrules (
period_id int(255) NOT NULL,
lt_id int(255) NOT NULL,
lt_year year(4) NOT NULL DEFAULT '0000',
rule_name varchar(45) NOT NULL,
night_of_stay int(10) NOT NULL,
fixed_rate float(10,2) NOT NULL,
PRIMARY KEY (period_id,lt_id,night_of_stay),
FOREIGN KEY (period_id) REFERENCES lt_hperiods(period_id) ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO lt_hdefaults (lt_year, lt_id, period_name, min_stay, max_stay, weekly_rate, nightly_rate, min_guests, max_guests, surchargeAboveGuests, chargePerGuestFee, bondFee, cleaningFee, bookingServiceFee) VALUES
(2010, 2, 'Default', 0, 0, 1200.00, 171.43, 2, 5, 6, 85.00, 1000.00, 120.00, 0.00),
(2010, 3, 'Default', 0, 0, 1300.00, 185.71, 2, 5, 6, 44.00, 1000.00, 120.00, 0.00);
INSERT INTO lt_hperiods (period_id, lt_id, lt_year, period_name, min_stay, max_stay, fromDate, toDate, weekly_rate, nightly_rate, arriveDepartDays, noArriveDepartDays) VALUES
(105, 3, 2010, 'winter', 2, 66, '2010-12-22', '2011-01-15', 1500.00, 214.29, 'Monday,Tuesday,Wednesday,Thursday', 'Friday,Saturday,Sunday'),
(106, 3, 2010, 'summer', 2, 77, '2011-01-14', '2011-01-28', 4000.00, 571.43, 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday', 'Sunday');
INSERT INTO lt_hrules (period_id, lt_id, lt_year, rule_name, night_of_stay, fixed_rate) VALUES
(106, 3, 2010, 'r2', 2, 222.00),
(106, 3, 2010, 'r1', 1, 111.00),
(105, 3, 2010, 'r2', 2, 222.00),
(105, 3, 2010, 'r1', 1, 111.00);
推荐答案
ENGINE=MyISAM
<-MyISAM不支持任何类型的外键.请改用InnoDB.
ENGINE=MyISAM
<-- MyISAM doesn't support any kind of foreign keys. Use InnoDB instead.
这篇关于我的"ON DELETE CASCADE"无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!