MySql query error 150 puzzle

Question

Im having to create and use a database for a class project. I developed the database and application locally using mysql workbench. It needs to be hosted on another service called myphpmyadmin. the problem is when I create my tables using the mysql execution line in that service I get the following error.

 Error
SQL query:

CREATE TABLE IF NOT EXISTS  `artists_residence` (

 `artists_id` INT( 11 ) ,
 `city_id` INT( 11 ) ,
PRIMARY KEY (  `artists_id` ,  `city_id` ) ,
CONSTRAINT FOREIGN KEY (  `artists_id` ) REFERENCES  `artists` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT FOREIGN KEY (  `city_id` ) REFERENCES  `city` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB CHARSET = Latin1;

MySQL said: Documentation

#1005 - Can't create table 'fondellb-db.artists_residence' (errno: 150) 

Here is the entire file of mySql i'm trying to execute.

CREATE TABLE IF NOT EXISTS `artists` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(255) NOT NULL,
  `lname` VARCHAR(255) NOT NULL,
  `year_of_birth` DATE NOT NULL,
  `year_of_death` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `country` (
  `country_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`country_id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `city` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `fk_country_id` INT(11),
  PRIMARY KEY (`id`, `fk_country_id`),
  CONSTRAINT `country_id`
    FOREIGN KEY (`fk_country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB CHARSET = Latin1;

CREATE TABLE IF NOT EXISTS `Medium` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `artists_medium` (
  `artists_id` INT(11),
  `medium_id` INT(11),
  PRIMARY KEY (`artists_id`, `medium_id`),
  CONSTRAINT 
    FOREIGN KEY (`artists_id`)
    REFERENCES `artists` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT 
    FOREIGN KEY (`medium_id`)
    REFERENCES `Medium` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARSET = Latin1;

CREATE TABLE IF NOT EXISTS `artists_residence` (
  `artists_id` INT(11),
  `city_id` INT(11),
  PRIMARY KEY (`artists_id`, `city_id`),
  CONSTRAINT 
    FOREIGN KEY (`artists_id`)
    REFERENCES `artists` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT 
    FOREIGN KEY (`city_id`)
    REFERENCES `city` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARSET = Latin1;

I cant figure out what is failing in the final query. All the other queries are successful and the tables were created. Ive fiddled with that query in all sorts of variations and nothing seems to solve the error. Ive removed the explicit indexes, Ive changed the order of the constraints, checked all my type comparisons. This is a file that mysql exported based on the model i designed so I'm not sure why now i'm receiving this error. Please do not refer me to a reference to the error code or to another post that also receives this error code, I know what can cause the error but im wondering what is causing it here.

Answer 1

In this case - you're creating the following foreign key

CONSTRAINT FOREIGN KEY (  `city_id` ) REFERENCES  `city` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE

while city table does not have an id primary key, but a composite primary key.

Obviously, the only one column of the 2 cannot guarantee uniqueness of the value hence mysql restricts you from creating a such FK.

So you must either change the city's PK or the artists_residence's FK.