
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.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »