Re: Two Foreing key from the same table / column
Excuse me, now it's ok, I forgotted an "UNSIGNED" in "`idTienda` TINYINT NULL",
Thanks,
Juan Perez Estrada Wrote:
-------------------------------------------------------
> 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.