Problema con claves ajenas

[kavalerian]

Buenas a todos.

Estoy creando una base de datos desde cero y he podido generar todas las Fk correctamente a excepción de la tabla even_cal_emp (ver mas abajo el código) que me salta el error 1005.
Necesito ayuda, esoy volviendome loco y el código está correcto.
Nota: Estoy usando InnoDB

Código PHP:

  --
-- primero creo la tabla maestra
--
CREATE TABLE `grupo_empresas` (
  `GRE_ID` int(10) NOT NULL auto_increment,
  `GRE_CODIGO` int(5) NOT NULL,
  `GRE_USUARIO_WEB` int(10) NOT NULL default '0',
  PRIMARY KEY  (`GRE_CODIGO`,`GRE_USUARIO_WEB`),
  UNIQUE KEY `GRE_ID_UNIQUE` (`GRE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20;
--
-- luego su detalle
--
CREATE TABLE `empresas` (
  `EMP_ID` int(10) NOT NULL auto_increment COMMENT 'Identificador de la empresa',
  `EMP_CODIGO` int(5) NOT NULL default '0' COMMENT 'Codigo de la empresa',
  `EMP_COD_GRUPO_EMP` int(5) NOT NULL default '0',
  `GRE_USUARIO_WEB` int(10) NOT NULL default '0',
  PRIMARY KEY  (`EMP_CODIGO`,`EMP_COD_GRUPO_EMP`,`GRE_USUARIO_WEB`),
  UNIQUE KEY `EMP_ID_UNIQUE` (`EMP_ID`),
  KEY `INDICE` (`EMP_ID`),
  KEY `FK_EMP_GRU` (`EMP_COD_GRUPO_EMP`,`GRE_USUARIO_WEB`),
  CONSTRAINT `FK_EMP_GRU` FOREIGN KEY (`EMP_COD_GRUPO_EMP`, `GRE_USUARIO_WEB`) REFERENCES `grupo_empresas` (`GRE_CODIGO`, `GRE_USUARIO_WEB`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15;
--
-- Esta es detalle de empresas
--
CREATE TABLE `calendario_empresa` (
  `CAE_ID` int(10) NOT NULL auto_increment,
  `CAE_CODIGO` int(10) NOT NULL default '0',
  `CAE_COD_EMPRESA` int(5) NOT NULL default '0',
  `CAE_COD_GRUPO_EMP` int(5) NOT NULL default '0',
  `CAE_USUARIO_WEB` int(10) NOT NULL default '0',
  PRIMARY KEY  (`CAE_ID`,`CAE_CODIGO`,`CAE_COD_EMPRESA`,`CAE_COD_GRUPO_EMP`,`CAE_USUARIO_WEB`),
  KEY `FK_CAE_EMP` (`CAE_COD_EMPRESA`,`CAE_COD_GRUPO_EMP`,`CAE_USUARIO_WEB`),
  CONSTRAINT `FK_CAE_EMP` FOREIGN KEY (`CAE_COD_EMPRESA`, `CAE_COD_GRUPO_EMP`, `CAE_USUARIO_WEB`) REFERENCES `empresas` (`EMP_CODIGO`, `EMP_COD_GRUPO_EMP`, `GRE_USUARIO_WEB`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;
--
-- Esta Debería ser detalle de calendarios_empresas
-- Pero cuando creo su FK me salta el error "ERROR 1005: Can't create table '.\nominas\#sql-6cc_2.frm' (errno: 150)"
--
CREATE TABLE `even_cal_emp` (
  `EVE_ID` int(10) NOT NULL,
  `EVE_COD_CALENDAR` int(10) NOT NULL default '0',
  `EVE_COD_EMPRESA` int(5) NOT NULL default '0',
  `EVE_COD_GRUPO_EMP` int(5) NOT NULL default '0',
  `EVE_USUARIO_WEB` int(10) NOT NULL default '0',
  PRIMARY KEY  (`EVE_ID`,`EVE_COD_CALENDAR`,`EVE_USUARIO_WEB`,`EVE_COD_GRUPO_EMP`,`EVE_COD_EMPRESA`),
  KEY `FK_EVE_CAE` (`EVE_COD_CALENDAR`,`EVE_COD_EMPRESA`,`EVE_COD_GRUPO_EMP`,`EVE_USUARIO_WEB`),
  CONSTRAINT `FK_CAE_EMP` FOREIGN KEY (`CAE_CODIGO`,`CAE_COD_EMPRESA`,`CAE_COD_GRUPO_EMP`,`CAE_USUARIO_WEB`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;
--
-- También he probado a primero generar la tabla y después hacer un alter table con us FK 
--
CREATE TABLE `even_cal_emp` (
  `EVE_ID` int(10) NOT NULL,
  `EVE_COD_CALENDAR` int(10) NOT NULL default '0',
  `EVE_COD_EMPRESA` int(5) NOT NULL default '0',
  `EVE_COD_GRUPO_EMP` int(5) NOT NULL default '0',
  `EVE_USUARIO_WEB` int(10) NOT NULL default '0',
  PRIMARY KEY  (`EVE_ID`,`EVE_COD_CALENDAR`,`EVE_USUARIO_WEB`,`EVE_COD_GRUPO_EMP`,`EVE_COD_EMPRESA`)
) ENGINE=InnoDB;
--
-- Aqui es donde creo su FK
--
ALTER TABLE `nominas`.`even_cal_emp` 
  ADD CONSTRAINT `FK_EVE_CAE`
  FOREIGN KEY (`EVE_COD_CALENDAR` , `EVE_COD_EMPRESA` , `EVE_COD_GRUPO_EMP` , `EVE_USUARIO_WEB` )
  REFERENCES `nominas`.`calendario_empresa` (`CAE_CODIGO` , `CAE_COD_EMPRESA` , `CAE_COD_GRUPO_EMP` , `CAE_USUARIO_WEB` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `FK_EVE_CAE` (`EVE_COD_CALENDAR` ASC, `EVE_COD_EMPRESA` ASC, `EVE_COD_GRUPO_EMP` ASC, `EVE_USUARIO_WEB` ASC) ; 
   

Bueno, gracias a todos...

[gnzsoloyo]

Por lo pronto hay algunos errores de comprensión del uso de los AUTO_INCREMENT, de la sintaxis de FOREIGN KEY, y resulta poco claro el diseño de relaciones que estás planteando.
Por partes:

1) Los campos declarados como AUTO_INCREMENT deben ser forzosamente PRIMARY KEY en MySQL, o al menos deben formar parte de ella. En tu caso, estás violando esta regla de MySQL en la tabla empresas y grupo_empresas, y usandolo mal en calendario_empresa
En el primer caso, si pones un auto_increment, debe ser PK, caso contrario genera un error.
En el segundo debes recordar que los valores tomados por los campos autoincrement son únicos en la tabla para cada registro, por lo que usarlos para una PK de varios campos no sólo carece de sentido, sino que puede prermitir que varios registros distintos repitan los otros valores simplemente porque el AI no es el mismo, y no te olvides que una PK se evalúa en forma integral, no por el valor individual de sus campos.

2) La sintaxis de un FOREIGN KEY es: FOREIGN KEY(campo/s) REFERENCES tablaorigen(campos)
En tu caso no estás poniendo ni el REFERENCES ni el nombre de la tabla.

3) En referencia al diseño de labase propiamente dicho, me parece que no tienes claro el concepto de relaciones y dependencias, porque el que estás proponiendo es innecesariamente complicado.
Me explico: A mi entender, lo primero que existen son las Empresas; cada empresa pertenece a un Grupo y puede haber más de una Empresa en cada Grupo. A su vez una empresa tiene Eventos, pero los eventos no pertenecen sino que se programan para una empresa. Cada evento programado pertenece al calendario de una empresa.
Si esto se ajusta al modelo, entonces las relaciones son así:
- Existe una tabla denominada Grupo con su propia PK.
- La tabla Empresa tiene su PK y posee como FK la del Grupo, porque la Empresa es parte de un grupo pero en el grupo pueden haber más de una.
- Puede existir una tabla Evento, que describe genéricamente los eventos programables y extras, con su propia PK.
- La relación entre Empresa y Evento se expresa en la tabla Calendario_Empresa que tiene como PK las de Empresa y Evento, más un discriminante que puede ser autonumérico o ser la fecha si es evento es único por fecha, o numérico controlado por aplicación, si es incremental por grupos. Incluso, si el evento sólo puede hacerse una vez por empresa, el discriminante no sería necesario.

Una de las cosas a destacar en este caso es que no se necesita crear FK tan largas y complejas porque ciertos datos no necesitan ser parte de la la clave, aunque por cuestiones de proceso se requiera que aparezcan: el ID del usuario, por ejemplo, puede ser práctico, pero no necesario a la hora de crear la PK.
Lo importante para definir una PK es simple: Si para identificar perfectamente un registro en la tabla, uno o más campos pueden ser suficientes, el resto no pertenece a la PK.

Pero ciertamente, la tabla even_cal_emp, tal y como lo planteas, no se necesita porque tiene los mismos campos que la Calendario_Empresa. Su uso aparece como redundante.

[kavalerian]

Gracias por responder pero no estoy del todo de acuerdo contigo:

1) Los campos declarados como AUTO_INCREMENT deben ser forzosamente PRIMARY KEY en MySQL, o al menos deben formar parte de ella. En tu caso, estás violando esta regla de MySQL en la tabla empresas y grupo_empresas, y usándolo mal en calendario_empresa


¿Por que deben ser PK? Está generado en MySQL y funciona correctamente, por tanto no lo considero una regla

En el primer caso, si pones un auto_increment, debe ser PK, caso contrario genera un error.


Ya hemos visto que no es así

En el segundo debes recordar que los valores tomados por los campos auto_increment son únicos en la tabla para cada registro, por lo que usarlos para una PK de varios campos no sólo carece de sentido, sino que puede permitir que varios registros distintos repitan los otros valores simplemente porque el AI no es el mismo, y no te olvides que una PK se evalúa en forma integral, no por el valor individual de sus campos.

Esto exactamente no lo entiendo muy bien. Evidentemente lo que estas diciendo nunca puede darse ya que cada campo tiene un nombre único

2) La sintaxis de un FOREIGN KEY es: FOREIGN KEY(campo/s) REFERENCES tabla origen(campos)
En tu caso no estás poniendo ni el REFERENCES ni el nombre de la tabla.

Esta sintaxis está copia de un export que he realizado de la bbdd. Está probada y funciona correctamente.
Como he puesto al final de código indico las dos formas que he utilizado para realizar la FK.

3) En referencia al diseño de labase propiamente dicho, me parece que no tienes claro el concepto de relaciones y dependencias, ...

El diseño está perfectamente claro:
Por un lado tenemos una tabla que se llama grupo_empresas, en esta podremos introducir los datos de una sociedad, cooperativa...
Esta tabla tiene un detalle Empresas, de la que cuelgan todas las empresas que tiene una cooperativa, estas pueden ser 1 o N.
De la tabla Empresas sale la tabla calendario_empresas que contendrá los días con las acciones que se realizaran. Como en un día pueden realizarse muchas acciones tendremos la tabla eventos en los que guardaremos todas las acciones que tengamos y que irá enlazada a la tabla calendario.

Aunque agradezco la explicación que me has hecho sobre mi trabajo no me has aclarado nada de lo que preguntaba.

Gracias por todo