We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Migration and constraints

Hi,

I am aware that this issue has been mentioned before briefly but I was wondering if anybody could help me out. I am working on a Phalcon PHP project where we intend on using the migrations functionality to manage our schema changes (rightly so!).

However we are having a few issues surrounding our database constraints:

  • If we build the initial schema via SQL import, upon running the migration we hit an SQL error because it attempts to add a constraint that already exists.
  • If we then drop the schema and attempt to build it from scratch via the migration process, we hit an SQL error because it is attempting to add a foreign key constraint to a table that isn't yet created (classes are 'imported' in alphabetical order).

Is there anyway that we can ignore constraints for the duration of the migration and then re-apply them afterwards to avoid either of these issues? It would be really good if we could use the migrations tool as it is meant to be.

P.S. Phalcon is a great project and I am really enjoying integrating with it, so thanks for all the hard work and such good documentation so far!

Regards,

Adam.



18.5k
edited Mar '14

First of all you must plan your migrations correctly according to your database data. For example, you must trigger situations when changing table users with field external_id that is int(5) to varchar(10), and setting your back migration again on int(5). In that case you must remember that on down migration this field can hold identical values (test123, more123, and123) and changing it to int will rise an duplicate error (coz you will have 123, 123, 123).

You have several ways:

  • correctly write you migrations.
  • create database schema generation according to some tables metadata (this will ignore tables data, but it will be more safe for structure).
  • create database schema generation and migrations for data (setup smart update feature that will depends your migrations on schema update).
  • avoid 'down' migrations and write only 'up'.
  • or forget about safe data and use https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql but it's extremely dangerous


1.8k

Hi Ivan,

Many thanks for your help understanding the process.

However I am not too sure that these issues should affect me as I only have 'up' migration functions declared, infact this is my first initial migration, I was under the impression that it should still run, even with minimal changes or infact build the schema for me given an empty database.

Adam.