Isi dari table ServIntervalMap adalah sebagai berikut
CREATE TABLE IF NOT EXISTS `ServIntervalMap` ( `ClientServIntervalMapID` int(11) NOT NULL AUTO_INCREMENT, `ClientID` int(11) NOT NULL, `ComponentModelID` int(11) NOT NULL, `ServiceIntervalStandard` int(11) NOT NULL, `ServiceIntervalMeasureID` int(11) NOT NULL, `OverhaulIntervalStandard` int(11) NOT NULL, `OverhaulIntervalMeasureID` int(11) NOT NULL, `MidlifeIntervalStandard` int(11) NOT NULL, `MidlifeIntervalMeasureID` int(11) NOT NULL, `CreatedBy` varchar(20) NOT NULL, `CreateDate` datetime NOT NULL, `IsActive` enum('Yes','No') NOT NULL, PRIMARY KEY (`ClientServIntervalMapID`), KEY `ClientID` (`ClientID`), KEY `ComponentModelID` (`ComponentModelID`), KEY `ServiceIntervalMeasureID` (`ServiceIntervalMeasureID`), KEY `OverhaulIntervalMeasureID` (`OverhaulIntervalMeasureID`), KEY `MidlifeIntervalMeasureID` (`MidlifeIntervalMeasureID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
perintah untuk menghapus kolom MySQL adalah
ALTER TABLE ServIntervalMap DROP ServiceIntervalMeasureID
tapi perintah diatas menghasilkan error
ERROR 1025 (HY000): Error on rename of './fokus/#sql-dce_85' to './fokus/ServIntervalMap' (errno: 150) MariaDB [fokus]> Ctrl-C -- exit!on_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION
cara untuk mengatasinya adalah, dengan mengecek foreign_keynya terlebih dahulu dengan perintah
SHOW CREATE TABLE ServiceIntervalMeasure;disini kita dapat nama foreign key dari ServIntervalMap seperti dibawah ini
CREATE TABLE `ServIntervalMap` ( `ClientServIntervalMapID` int(11) NOT NULL AUTO_INCREMENT, `ClientID` int(11) NOT NULL, `ComponentModelID` int(11) NOT NULL, `ServiceIntervalStandard` int(11) NOT NULL, `ServiceIntervalMeasureID` int(11) NOT NULL, `OverhaulIntervalStandard` int(11) NOT NULL, `OverhaulIntervalMeasureID` int(11) NOT NULL, `MidlifeIntervalStandard` int(11) NOT NULL, `MidlifeIntervalMeasureID` int(11) NOT NULL, `CreatedBy` varchar(20) NOT NULL, `CreateDate` datetime NOT NULL, `IsActive` enum('Yes','No') NOT NULL, PRIMARY KEY (`ClientServIntervalMapID`), KEY `ClientID` (`ClientID`), KEY `ComponentModelID` (`ComponentModelID`), KEY `ServiceIntervalMeasureID` (`ServiceIntervalMeasureID`), KEY `OverhaulIntervalMeasureID` (`OverhaulIntervalMeasureID`), KEY `MidlifeIntervalMeasureID` (`MidlifeIntervalMeasureID`), CONSTRAINT `ServIntervalMap_ibfk_5` FOREIGN KEY (`MidlifeIntervalMeasureID`) REFERENCES `measure` (`MeasureID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `ServIntervalMap_ibfk_1` FOREIGN KEY (`ClientID`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `ServIntervalMap_ibfk_2` FOREIGN KEY (`ComponentModelID`) REFERENCES `component_model` (`component_model_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `ServIntervalMap_ibfk_3` FOREIGN KEY (`ServiceIntervalMeasureID`) REFERENCES `measure` (`MeasureID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `ServIntervalMap_ibfk_4` FOREIGN KEY (`OverhaulIntervalMeasureID`) REFERENCES `measure` (`MeasureID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
sekarang hapus foreign colum ServiceIntervalMeasureID dengan cara
ALTER TABLE ServIntervalMap DROP foreign key ServIntervalMap_ibfk_3
terakhir hapus kolom tersebut dari table
ALTER TABLE ServIntervalMap DROP ServiceIntervalMeasureID;