unable to create table errno150

Question

I am getting error while creating table "register"... there are 5 tables astro-info, expectations, familybackground, personal_info, service-business

I have checked data types and size of all foreign keys. They are the same. I.e. bigint(100) for all primaty keys in other tables. But still I am getting this error...

Please help ...

Here is my table structure ...

create table register(
    register_id bigint(100) primary key,
    mv_id bigint(100),
    astro_id bigint(100),
    foreign key(astro_id) 
            references `astro-info`(astro_id),
    expectation_id bigint(100),
    foreign key(expectation_id) 
            references `expectations`(expectation_id),
    familybackground_id bigint(100),
    foreign key(familybackground_id) 
            references `familybackground`(familybackground_id),
    personal_info_id bigint(100),
    foreign key(personal_info_id) 
            references `personal_info`(personal_info_id),
    service_id bigint(100),
    foreign key(service_id) 
            references `service-business`(service_id));

Table structure for table astro-info

CREATE TABLE IF NOT EXISTS `astro-info` (
  ` astro_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (` astro_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Table structure for table expectations

CREATE TABLE IF NOT EXISTS `expectations` (
  `expectation_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (`expectation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Table structure for table familybackground

CREATE TABLE IF NOT EXISTS `familybackground` (
  `familybackground_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (`familybackground_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9

Table structure for table personal_info

CREATE TABLE IF NOT EXISTS `personal_info` (
  `personal_info_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (`personal_info_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10

Table structure for table service-business

CREATE TABLE IF NOT EXISTS `service-business` (
  `service_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (`service_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 

Answer 1

There was a bug in your table astro-info.

CREATE TABLE IF NOT EXISTS `astro-info` (
  ` astro_id` bigint(100) NOT NULL AUTO_INCREMENT,
  -- other irrelevant fields removed from here
  PRIMARY KEY (` astro_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Error was in 2 places:

  ` astro_id` bigint(100) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (` astro_id`)

It had a leading space with column name. And the same was not used while defining a foreign key on the column in regster table.

create table register(
    register_id bigint(100) primary key,
    mv_id bigint(100),
    astro_id bigint(100),
    foreign key(astro_id) 
            references `astro-info`(astro_id),
    expectation_id bigint(100),
    foreign key(expectation_id) 
            references `expectations`(expectation_id),
    familybackground_id bigint(100),
    foreign key(familybackground_id) 
            references `familybackground`(familybackground_id),
    personal_info_id bigint(100),
    foreign key(personal_info_id) 
            references `personal_info`(personal_info_id),
    service_id bigint(100),
    foreign key(service_id) 
            references `service-business`(service_id));

This definition in register table

    astro_id bigint(100),
    foreign key(astro_id) 
            references `astro-info`(astro_id),

does not match with following definition in astro-info table

  ` astro_id` bigint(100) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (` astro_id`)

Solution:
Option 1: Redifine ` astro_id` to `astro_id` in astro-info table.

Option 2: Redifine astro_id to have a leading space with it. As in ` astro_id`.

My suggestion is to go with Option 1, modify the astro-info table field.