Two Foreing key from the same table / column
Please, I have problems with this create statement (sorry for my poor english)
-- -----------------------------------------------------
-- Table `gemalia`.`Cliente`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gemalia`.`Cliente` (
`idCliente` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idTienda` TINYINT NULL ,
`idTiendaAlta` TINYINT UNSIGNED NULL ,
`idTipoVenta` TINYINT UNSIGNED NULL ,
`idFormaPago` TINYINT UNSIGNED NULL ,
`idTipoIVA` TINYINT UNSIGNED NULL ,
`idTipoCliente` TINYINT UNSIGNED NOT NULL DEFAULT 1 ,
`Codigo` CHAR(10) NOT NULL DEFAULT 'Sin Codigo' ,
`FechaBaja` DATE NULL ,
PRIMARY KEY (`idCliente`) ,
INDEX Cliente_FKIndex1 (`idTipoCliente` ASC) ,
INDEX Cliente_FKIndex2 (`idTipoIVA` ASC) ,
INDEX Cliente_FKIndex3 (`idFormaPago` ASC) ,
INDEX Cliente_FKIndex4 (`idTipoVenta` ASC) ,
INDEX Cliente_FKIndex5 (`idTienda` ASC) ,
INDEX Rel_89 (`idTiendaAlta` ASC) ,
CONSTRAINT `Rel_24`
FOREIGN KEY (`idTipoCliente` )
REFERENCES `gemalia`.`TipoCliente` (`idTipoCliente` ),
CONSTRAINT `Rel_87`
FOREIGN KEY (`idTipoIVA` )
REFERENCES `gemalia`.`TipoIVA` (`idTipoIVA` ),
CONSTRAINT `Rel_30`
FOREIGN KEY (`idFormaPago` )
REFERENCES `gemalia`.`FormaPago` (`idFormaPago` ),
CONSTRAINT `Rel_70`
FOREIGN KEY (`idTipoVenta` )
REFERENCES `gemalia`.`TipoVenta` (`idTipoVenta` ),
CONSTRAINT `Rel_88`
FOREIGN KEY (`idTienda` )
REFERENCES `gemalia`.`Tienda` (`idTienda` ),
CONSTRAINT `Rel_89`
FOREIGN KEY (`idTiendaAlta` )
REFERENCES `gemalia`.`Tienda` (`idTienda` ))
ENGINE = InnoDB
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
As you see, the last two Constraints focus on the same column: Tienda.idTienda. One of the is to know in which Shop my client has been created (idTiendaAlta), and the other one is to control in which shops my client can buy (idTienda) (if last one is null it means that my client can buy in all the shop).
Could you help me ? , I get:
#1005 - Can't create table '.\gemalia\cliente.frm' (errno: 150)
All the tables are created before, but Cliente not. Why ?????
Thanks a lot.