#1503 new
Dmitry Ulupov

auto_upgrade! on empty Mysql database does not create foreign key constraints

Reported by Dmitry Ulupov | April 11th, 2011 @ 03:31 AM

I am using dm-constraints plugin on Mysql and auto_upgrade! on empty database is not creating foreign key constraints.
But auto_migrate! does.

Probably #1493 is related to this.

Steps to reproduce:

1) Create empty db (fktest, in this example) on mysql (I used version 5.5.10).
2) Run this in irb:

require 'dm-core'
require 'dm-constraints'
require 'dm-migrations'
DataMapper::Logger.new($stdout, :debug)
DataMapper.setup(:default, 'mysql://root@localhost/fktest')
class Blog
    include DataMapper::Resource
    property :id, Serial
    has n, :posts, :constraint => :protect
end
class Post
    include DataMapper::Resource
    property :id, Serial
    belongs_to :blog
end
DataMapper.finalize

3) After DataMapper.auto_upgrade! no foreign key in sight:

~ (0.000065) SET sql_auto_is_null = 0
~ (0.000061) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
~ (0.000247) SHOW TABLES LIKE 'blogs'
~ (0.000071) SET sql_auto_is_null = 0
~ (0.000059) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
~ (0.000340) SHOW VARIABLES LIKE 'character_set_connection'
~ (0.000242) SHOW VARIABLES LIKE 'collation_connection'
~ (0.049769) CREATE TABLE `blogs` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
~ (0.000220) SHOW TABLES LIKE 'posts'
~ (0.090965) CREATE TABLE `posts` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `blog_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
~ (0.090156) CREATE INDEX `index_posts_blog` ON `posts` (`blog_id`)

4) But after DataMapper.auto_migrate! foreign key will be created:

~ (0.000065) SET sql_auto_is_null = 0
~ (0.000067) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
~ (0.000282) SHOW TABLES LIKE 'blogs'
~ (0.000146) SHOW TABLES LIKE 'posts'
~ (0.000130) DROP TABLE IF EXISTS `blogs`
~ (0.000136) SHOW TABLES LIKE 'blogs'
~ (0.000065) SET sql_auto_is_null = 0
~ (0.000062) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
~ (0.000380) SHOW VARIABLES LIKE 'character_set_connection'
~ (0.000404) SHOW VARIABLES LIKE 'collation_connection'
~ (0.589754) CREATE TABLE `blogs` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
~ (0.030235) DROP TABLE IF EXISTS `posts`
~ (0.010516) SHOW TABLES LIKE 'posts'
~ (0.438719) CREATE TABLE `posts` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `blog_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
~ (0.490992) CREATE INDEX `index_posts_blog` ON `posts` (`blog_id`)
~ (0.002673) SELECT COUNT(*) FROM `information_schema`.`table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' AND `table_schema` = 'fktest' AND `table_name` = 'posts' AND `constraint_name` = 'posts_blog_fk'
~ (0.090063) ALTER TABLE `posts` ADD CONSTRAINT `posts_blog_fk` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

No comments found

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

Pages