CODEWITHSUNDEEP
mysqlsymfonypdodoctrine-ormalter-table
french_dev
french_dev

Reputation: 2177

error1833: cannot change colum id

With prompt command, I have generated a database with doctine using reverse engineering. It worked perfectly:

php app/console doctrine:mapping:convert yml ./src/Gir/DatabaseBundle/Resources/config/doctrine/metadata/orm --from-database --force

After, I create the entities:

php app/console doctrine:mapping:import GirDatabaseBundle annotation

And the annotations:

php app/console doctrine:generate:entities GirDatabaseBundle

Then, I installed the new update of Symfony with composer.phar.

Then, the FOSUserBundle in order to manage users in my project.

I have to update my database with doctrine using this prompt command in order to generate the table users and the entities, etc:

php app/console doctrine:schema:update --force

When I do this command, doctrine send me this error in the windows prompt command:

**[Doctrine\DBAL\DBALException]**
An exception occured while executing 'ALTER TABLE agence CHANGE id id INT NOT NULL':
SQLSTATE[HY000]: General error: 1833 Cannot change colum 'id': used in a foreign key constraint 'fk_employe_agence1' of table 'gir.employe'

**[PDOException]**
SQLSTATE[HY000]: General error: 1833 Cannot change colum 'id': used in a foreign key constraint 'fk_employe_agence1' of table 'gir.employe'

This the SQL of agence table:

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

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

--
-- Structure de la table `agence`
--

CREATE TABLE IF NOT EXISTS `agence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(150) NOT NULL,
  `nomVoie` varchar(150) NOT NULL,
  `numVoie` int(5) DEFAULT NULL,
  `codePostal` int(5) NOT NULL,
  `comune` varchar(150) NOT NULL,
  `telephone` varchar(150) NOT NULL,
  `fax` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `entreprises_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`entreprises_id`),
  KEY `fk_agence_entreprises1_idx` (`entreprises_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

And this is the SQL of employe table:

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

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

--
-- Structure de la table `employe`
--

CREATE TABLE IF NOT EXISTS `employe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(45) NOT NULL,
  `prenom` varchar(45) NOT NULL,
  `poste` varchar(45) NOT NULL,
  `portable` varchar(45) NOT NULL,
  `ligneDirecte` varchar(45) NOT NULL,
  `faxDirect` varchar(45) NOT NULL,
  `email` varchar(150) NOT NULL,
  `etat` tinyint(1) NOT NULL,
  `agence_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`agence_id`),
  KEY `fk_employe_agence1_idx` (`agence_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Contraintes pour la table `employe`
--
ALTER TABLE `employe`
  ADD CONSTRAINT `fk_employe_agence1` FOREIGN KEY (`agence_id`) REFERENCES `agence` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Someone should why and how to fix this with doctrine?

Upvotes: 0

Views: 3338

Answers (3)

juanitourquiza
juanitourquiza

Reputation: 2194

This Solution is correct: https://stackoverflow.com/a/26628581/2400373

However if you work on Symfony 3, the correct is this:

bin/console doctrine:schema:update --dump-sql --complete >> dump.sql

Never used the instruction php before this instruction.

Upvotes: 1

AlexandruSerban
AlexandruSerban

Reputation: 312

If you want to use this as a DataFixture here is how I've done it in the load method:

    $dumpFilePath  = "dump.sql";
    file_put_contents($dumpFilePath, "SET FOREIGN_KEY_CHECKS = 0; \n");

    $kernel = $this->container->get('kernel');
    $application = new Application($kernel);
    $application->setAutoExit(false);
    $options = array('command' => 'doctrine:schema:update', '--dump-sql' => true, '--complete' => true);

    $input = new ArrayInput($options);
    $output = new StreamOutput(fopen($dumpFilePath, 'a'));
    $application->run($input, $output);

    $entityManager->getConnection()->prepare(file_get_contents($dumpFilePath))->execute();

Upvotes: 0

lxg
lxg

Reputation: 13107

The problem with the foreign keys is that Doctrine sometimes can't resolve them itself. But it usually works of you temporarily disable FKs. Try the following:

Prepare an SQL dump which will disable FK checks during import. Then tell Doctrine to dump the changes into a file, instead of applying them directly. Then import the file with MySQL.

# disable FK checks during the import
echo 'SET FOREIGN_KEY_CHECKS = 0;' > dump.sql

# append the DB dump
app/console doctrine:schema:update --dump-sql --complete >> dump.sql

# import the dump into MySQL
mysql -u username -p -D dbname < dump.sql

If that works without throwing errors, have Doctrine check if everything's consistent with the ORM definitions:

app/console doctrine:schema:update --dump-sql --complete

If this gives you any more SQL queries to execute, then do so. After that, you should be fine.

Upvotes: 4

Related Questions