MYSQL error in creating foreign key constraint

Question

I am using MySQL to create a small database. and facing some issues in foreign keys and primary key. I really don't understand as it seems a simple problem.

CREATE  TABLE IF NOT EXISTS `db_trimms`.`urban_area` (
  `area_id` INT(10)  NOT NULL ,
  `city` VARCHAR(60) NOT NULL ,
  `state` VARCHAR(60) NOT NULL ,
  `urban_area` VARCHAR(60) NOT NULL ,
  `census_region` VARCHAR(60) NOT NULL ,
  `area_no` VARCHAR(60) NOT NULL ,
  `freeway_speed` VARCHAR(60) NOT NULL ,
  `arterial_speed` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`area_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

its running successfully. But while create another table and creating foreign key referring to the area_id of the above table...its creating issues...

#1005 - Can't create table 'db_trimms.emiss_others_offpeak' (errno: 150) (Details...)

and here is the query

CREATE  TABLE IF NOT EXISTS `db_trimms`.`emiss_others_offpeak` (
  `area_id` INT(10) UNSIGNED NOT NULL ,
  `ammonia` VARCHAR(60) NOT NULL ,
  `atm_carbon_dio` VARCHAR(60) NOT NULL ,
  `carbon_dio_equiv` VARCHAR(60) NOT NULL ,
  `carbon_mono` VARCHAR(60) NOT NULL ,
  `methane` VARCHAR(60) NOT NULL ,
  `nitrogen_dio` VARCHAR(60) NOT NULL ,
  `nitrogen_oxide` VARCHAR(60) NOT NULL ,
  `nitrous_oxide` VARCHAR(60) NOT NULL ,
  `non_meth_hydrocarbs` VARCHAR(60) NOT NULL ,
  `oxides_of_nitrogen` VARCHAR(60) NOT NULL ,
  `particulate_matter_pm10` VARCHAR(60) NOT NULL ,
  `particulate_matter_pm2_5` VARCHAR(60) NOT NULL ,
  `sulfate` VARCHAR(60) NOT NULL ,
  ` sulfur_dioxide` VARCHAR(60) NOT NULL ,
  `total_hydrocarbon` VARCHAR(60) NOT NULL ,
  `vol_org_comp` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`area_id`) ,
  CONSTRAINT `fk_others_offpeak_urban`
    FOREIGN KEY (`area_id` )
    REFERENCES `db_trimms`.`urban_area` (`area_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

I don't think you can make a foreign key reference to another database. — user610217
– user610217, Commented Apr 22, 2013 at 15:51

John Woo · Accepted Answer · 2013-04-22 15:58:50Z

1

The data types are incompatible with each other. Make column area_id from table urban_area also UNSIGNED.

`area_id` INT(10) UNSIGNED NOT NULL ,

SQLFiddle Demo

edited Apr 22, 2013 at 15:58

answered Apr 22, 2013 at 15:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL error in creating foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related