
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
...
end
class Invoice
include DataMapper::Resource
property :id, Serial
has n, :activities
...
end
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) 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.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »