Can not delete FK/ Constraint (index)

Question

I have a table (see below) in which I added a compound foreig key to another table. I can not add rows to this table because the foreign key fails, even if the parent key has a record with the compound index values... So the table currently is empty.

The foreign key is realy not needed and the system would suffice with one Fk to the other table ID, so I tryed to my best to delete the foreign key to no avail, always getting the infamus 150 err.

Tried to stop foregn key check and then to delete the index, columns, bur could not delete either

This is may table...

CREATE TABLE `Servicios` (
 `Servicio_ID` int(10) unsigned NOT NULL auto_increment,
 `SolSer_ID` int(10) unsigned NOT NULL,
 `ConsecutivoServicio` int(10) unsigned default NULL,
 `ServicioAnt_ID` int(10) unsigned default NULL,
 `FechaAsignada` date NOT NULL COMMENT 'Fecha Asignada para el servicio',
 `HoraAsignada` time NOT NULL COMMENT 'Hora Asignada para hacer el servicio',
 `FechaServicio` date default NULL,
 `HoraServicio` time default NULL,
 `Tecnico_ID` int(10) unsigned NOT NULL,
 `Eq_ID` int(10) unsigned default NULL,
 `ObjetoReporte_ID` int(10) unsigned NOT NULL,
 `TipoFalla_ID` int(10) unsigned NOT NULL,
 `EnergiaRegulada` enum('Si','No') collate utf8_spanish_ci NOT NULL,
 `EstadoAlLlegar` varchar(500) collate utf8_spanish_ci default NULL,
 `DiagnosticoFalla` varchar(500) collate utf8_spanish_ci default NULL,
 `SolProp` varchar(500) collate utf8_spanish_ci default NULL,
 `Observaciones` varchar(500) collate utf8_spanish_ci default NULL,
 `ResultadoServ_ID` int(10) unsigned default NULL,
 `RetiroEquipo` enum('No','Si') collate utf8_spanish_ci default NULL,
 `EstatusServ_ID` int(10) unsigned default NULL,
 `RecibeServicioInt` int(10) unsigned default NULL,
 `PuestoRecibeInt` int(10) unsigned default NULL,
 `FechaRecibe` date default NULL,
 `HoraRecibe` time default NULL,
 `EstatusSolServ_ID` int(10) unsigned NOT NULL,
 `Empresa_ID` int(10) unsigned NOT NULL,
 `Empleado_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY  (`Servicio_ID`),
 UNIQUE KEY `FK_Servicios_SolServicio2` (`SolSer_ID`,`Empresa_ID`),
 KEY `Tecnicos` (`Tecnico_ID`),
 KEY `FK_Servicios_ResultadosServicio` (`ResultadoServ_ID`),
 KEY `FK_Servicios_EstatusSolServicios` (`EstatusSolServ_ID`),
 KEY `FK_Servicios_Servicios` (`ServicioAnt_ID`),
 KEY `FK_Servicios_EstatusServicio` (`EstatusServ_ID`),
 KEY `FK_Servicios_TiposFalla` (`TipoFalla_ID`),
 KEY `FK_Servicios_ObjetoReporte` (`ObjetoReporte_ID`),
 KEY `FK_Servicios_Equipos` (`Eq_ID`),
 KEY `FK_Servicios_Empleados` (`Empleado_ID`),
 KEY `Empresa_ID` (`Empresa_ID`),
 **CONSTRAINT `FK_Servicios_SolServicio` FOREIGN KEY (`SolSer_ID`, `Empresa_ID`) REFERENCES `solservicio` (`SolSer_ID`, `Empresa_ID`),**
 CONSTRAINT `Servicios_ibfk_37` FOREIGN KEY (`SolSer_ID`) REFERENCES `SolServicio` (`SolSer_ID`),
 CONSTRAINT `Servicios_ibfk_38` FOREIGN KEY (`Tecnico_ID`) REFERENCES `Empleados` (`Empleado_ID`),
 CONSTRAINT `Servicios_ibfk_39` FOREIGN KEY (`ObjetoReporte_ID`) REFERENCES `ObjetoReporte` (`ObjetoReporteID`),
 CONSTRAINT `Servicios_ibfk_40` FOREIGN KEY (`TipoFalla_ID`) REFERENCES `TiposFalla` (`TipoFalla_ID`),
 CONSTRAINT `Servicios_ibfk_41` FOREIGN KEY (`ResultadoServ_ID`) REFERENCES `ResultadosServicio` (`ResServ_ID`),
 CONSTRAINT `Servicios_ibfk_42` FOREIGN KEY (`EstatusServ_ID`) REFERENCES `EstatusServicio` (`EstatusServ_ID`),
 CONSTRAINT `Servicios_ibfk_43` FOREIGN KEY (`EstatusSolServ_ID`) REFERENCES `EstatusSolServicios` (`EstatusSolServ_ID`),
 CONSTRAINT `Servicios_ibfk_44` FOREIGN KEY (`Empresa_ID`) REFERENCES `Empresas` (`Empresa_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

The offending constraint in bold. I tried

SET foreign_key_checks = 0;

ALTER TABLE `Servicios` DROP FOREIGN KEY `FK_Servicios_SolServicio2`;

and

ALTER TABLE `Servicios` DROP INDEX `FK_Servicios_SolServicio2`;

But keep getting:

#1025 - Error on rename of './segucom_SS8/#sql-2ca6_af6829' to './segucom_SS8/Servicios' (errno: 150)

Funny thing is the create table refers to this constraint as FK_Servicios_SolServicio and not as: FK_Servicios_SolServicio2 (missing 2 at the end)

Here is the other table

CREATE TABLE SolServicio ( SolSer_ID int(10) unsigned NOT NULL auto_increment, Empresa_ID int(10) unsigned NOT NULL, FechaSolicitud datetime NOT NULL, HoraSolicitud time NOT NULL, Solicito varchar(20) collate utf8_spanish_ci NOT NULL, SolVia_ID int(10) unsigned NOT NULL, DescripcionFalla varchar(264) collate utf8_spanish_ci NOT NULL, Empleado_ID int(10) unsigned NOT NULL, TipoServ_ID int(10) unsigned NOT NULL, EstatusSolServ_ID int(10) unsigned NOT NULL, ServicioInicial int(10) unsigned default NULL, PRIMARY KEY (SolSer_ID,Empresa_ID), KEY IX_SolServicio2 (Empleado_ID), KEY IX_SolServicio3 (Empresa_ID), KEY FK_SolServicio_TiposServicio (TipoServ_ID), KEY FK_SolServicio_EstatusSolServicios (EstatusSolServ_ID), KEY FK_SolServicio_SolicitudVia (SolVia_ID), KEY FK_SolServicio_Empleados (Empleado_ID), CONSTRAINT SolServicio_ibfk_1 FOREIGN KEY (Empresa_ID) REFERENCES Empresas (Empresa_ID), CONSTRAINT SolServicio_ibfk_2 FOREIGN KEY (SolVia_ID) REFERENCES SolicitudVia (SolVia_ID), CONSTRAINT SolServicio_ibfk_3 FOREIGN KEY (Empleado_ID) REFERENCES Empleados (Empleado_ID), CONSTRAINT SolServicio_ibfk_4 FOREIGN KEY (TipoServ_ID) REFERENCES TiposServicio (TipoServ_ID), CONSTRAINT SolServicio_ibfk_5 FOREIGN KEY (EstatusSolServ_ID) REFERENCES EstatusSolServicios (EstatusSolServ_ID) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

miherrma miherrma · Accepted Answer · 2011-11-15T07:28:54

Hmm, to me it looks like a typo: you have a Unique Key with the name of the foreign key you try to remove (FK_Servicios_SolServicio2). And you have a foreign key with the name FK_Servicios_SolServicio that you marked with ** in your script. Obviously you cannot remove a unique key with DROP FOREIGN KEY. I would suggest to try again with

ALTER TABLE `Servicios` DROP FOREIGN KEY `FK_Servicios_SolServicio`;

Can not delete FK/ Constraint (index)

2 Answers