我的'ON DELETE CASCADE'不起作用

问题描述：

我有3个表格：lt_hdefaults,lt_hperiods和lt_hrules。在lt_hdefaults中，特定年份的财产有一行。在lt_hperiods表中，可能有多个时期，称为季节。在lt_hrules表中，每个期间可能有多个规则。现在，我无法工作：当用户从lt_hdefaults删除记录时，其他与删除记录有关的数据应从lt_hperiods和lt_hrules表中删除。我试图通过使用我的'ON DELETE CASCADE'不起作用

FOREIGN KEY (lt_year,lt_id) REFERENCES lt_hdefaults(lt_year,lt_id) ON DELETE CASCADE

但是，它不起作用。我知道它看起来很长，但并不复杂。如果有人有任何想法，我将不胜感激。我知道如何使用MySQL，但我不是这方面的专家。非常感谢。下面

样品：

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。

感谢这么多，这与工作进一步修订。（对于谁面临着同样的问题，其他人，我也会添加另一个答案。） – user403295 2010-12-22 23:57:38

答

外键参考使用innoDB

答

像这样的东西works..as一个例子：

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 

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), 
    INDEX (lt_id), 
    INDEX (lt_year), 
    CONSTRAINT FOREIGN KEY (`lt_id`, `lt_year`) 
    REFERENCES lt_hdefaults(`lt_id`, `lt_year`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=107; 



SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 

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), 
    INDEX(period_id), 
    CONSTRAINT FOREIGN KEY (`period_id`) 
    REFERENCES lt_hperiods(`period_id`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我的'ON DELETE CASCADE'不起作用

相关推荐