#1236 ✓resolved
Jakub Suder

SQL error (can't create table) during migration with dm-constraints

Reported by Jakub Suder | April 7th, 2010 @ 08:59 AM | in 1.0.0

I have such two models (among others):

class Activity
  include DataMapper::Resource
  property :id,          Serial
  property :invoice_id,  Integer, :index => true
  belongs_to :invoice

class Invoice
  include DataMapper::Resource
  property :id,          Serial
  has n, :activities

If I include dm-constraints, during the migration it tries to execute such SQL:

 ALTER TABLE `activities` ADD CONSTRAINT `activities_invoice_fk` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

And then I get this error:

Can't create table 'rubytime3_dev.#sql-a4_53' (errno: 150) (code: 1005, sql state: HY000, query: ALTER TABLE `activities` ADD CONSTRAINT `activities_invoice_fk` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, uri: mysql://root@localhostrubytime3_dev?encoding=UTF-8)

show innodb status in mysql console tells me that:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

I assume that the primary key (serial) is indexed by default, so what's the problem? Is it that invoices.id is created as INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, and activities.invoice_id as INTEGER and these are different data types? In this case, am I doing something wrong or is DataMapper creating the columns incorrectly?

Comments and changes to this ticket

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) April 7th, 2010 @ 01:10 PM

    • State changed from “new” to “resolved”
    • Assigned user set to “Dan Kubb (dkubb)”
    • Milestone set to 1.0.0

    This is probably due to the fact that the FKs are being created explicitly, but in doing so, it is not the same as the PK (the PK is unsigned, while the FK is not).

    The recommended approach would be to not define invoice_id in Activity, and allow the FK inference process to handle it creating the property based on the PK in Invoice.

    The second approach, which is something I would only recommend if FK inference wasn't working for you, is you can define the property explicitly, but specify :min => 1, eg:

    property :invoice_id, Integer, :min => 1, :index => true

    I'm marking this as resolved for now. If this does not resolve your problem please reply with a stand-alone script that we can run to reproduce the problem, and I will reopen this ticket.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket