MySQL: #1005 - 无法创建表 (errno: 150)

Question

我在用mysql给一个表添加外键的时候遇到了一点问题，我在下面的代码中找不到错误:

-- phpMyAdmin SQL Dump
-- version 4.1.12
-- http://www.phpmyadmin.net
--
-- Client :  localhost
-- Généré le :  Lun 09 Novembre 2015 à 23:43
-- Version du serveur :  5.5.36-MariaDB-log
-- Version de PHP :  5.5.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Base de données :  `morina`
--

-- --------------------------------------------------------

--
-- Structure de la table `Etat`
--

CREATE TABLE IF NOT EXISTS `Etat` (
  `idetat` mediumint(8) NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) NOT NULL,
  PRIMARY KEY (`idetat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure de la table `Facture`
--

CREATE TABLE IF NOT EXISTS `Facture` (
  `idfacture` mediumint(8) NOT NULL AUTO_INCREMENT,
  `idpaiement` mediumint(8) DEFAULT NULL,
  `montant` int(11) DEFAULT NULL,
  `jour` datetime NOT NULL,
  PRIMARY KEY (`idfacture`),
  KEY `idpaiement` (`idpaiement`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure de la table `Paiement`
--

CREATE TABLE IF NOT EXISTS `Paiement` (
  `idp` mediumint(8) NOT NULL AUTO_INCREMENT,
  `intitule` varchar(255) NOT NULL,
  PRIMARY KEY (`idp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure de la table `Rangee`
--

CREATE TABLE IF NOT EXISTS `Rangee` (
  `idrangee` mediumint(8) NOT NULL AUTO_INCREMENT,
  `zone` varchar(1) NOT NULL,
  PRIMARY KEY (`idrangee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure de la table `TableSalle`
--

CREATE TABLE IF NOT EXISTS `TableSalle` (
  `idrangee` mediumint(8) NOT NULL,
  `position` mediumint(8) NOT NULL,
  `idetat` mediumint(8) NOT NULL,
  PRIMARY KEY (`idrangee`,`position`),
  KEY `idetat` (`idetat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `Facture`
--
ALTER TABLE `Facture`
  ADD CONSTRAINT `Facture_ibfk_1` FOREIGN KEY (`idpaiement`) REFERENCES `Paiement` (`idp`);

--
-- Contraintes pour la table `TableSalle`
--
ALTER TABLE `TableSalle`
  ADD CONSTRAINT `TableSalle_ibfk_1` FOREIGN KEY (`idrangee`) REFERENCES `Rangee` (`idrangee`),
  ADD CONSTRAINT `TableSalle_ibfk_2` FOREIGN KEY (`idetat`) REFERENCES `Etat` (`idetat`);


CREATE TABLE `AttributionTable` (

    `idrangee` mediumint(8) NOT NULL,
    `position` mediumint(8) NOT NULL,
    `idfacture` mediumint(8) NOT NULL,
  PRIMARY KEY (`idrangee`,`position`,`idfacture`)


)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `TableSalle`
--
ALTER TABLE `AttributionTable`
  ADD CONSTRAINT `Attribution_ibfk_1` FOREIGN KEY (`idrangee`,`position`) REFERENCES `TableSalle`(`idrangee`,`position`),
  --ADD CONSTRAINT `Attribution_ibfk_2` FOREIGN KEY (`position`) REFERENCES `TableSalle`(`position`),
  ADD CONSTRAINT `Attribution_ibfk_3` FOREIGN KEY (`idfacture`) REFERENCES `Facture`(`idfacture`);

感谢帮助! :)

Answer 1

Please read documentation here

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.