#1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same

Question

Last night I ran SQL commands in phpMyAdmin very similar to the following to setup a players table and a games table linked by a foreign key. It worked without errors, making google_id in my games table a foreign key.

CREATE TABLE players(
    google_id VARCHAR(36) PRIMARY KEY,
    display_name VARCHAR(255) NOT NULL,
    given_name VARCHAR(255) NOT NULL,
    family_name VARCHAR(255) NOT NULL,
    picture_url VARCHAR(2083) NOT NULL,
    leaderboard_initials VARCHAR(40),
    operating_system VARCHAR(255) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE games(
    game_id VARCHAR(255) PRIMARY KEY,
    google_id VARCHAR(36) NOT NULL,
    won INT(1) NOT NULL,
    score INT(10) NOT NULL,
    num_in_code INT(2) NOT NULL,
    num_colours INT(2) NOT NULL,
    num_guesses INT(2) NOT NULL,
    turn INT(2) NOT NULL,
    rank INT(10) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Then make the google_id a foreign key in games:

ALTER TABLE
    games ADD FOREIGN KEY(google_id) REFERENCES role(google_id);

Today the third command gave the following error:

Error
SQL query:


ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES role(google_id)
MySQL said: 

#1215 - Cannot add foreign key constraint

AFAIK my google_id is exactly the same data type so this answer hasn't helped me

I've also read this answer but can't see anything wrong with my three queries.

Can anybody spot my mistake?

EDIT: Sorry people, this was a typo. role should have been players

Answer 1

If games table is referring google_id from the parent table "players",then use below syntax (unless you are establishing Foreign key with "role" table as per your SQL statement):

ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES players(google_id);

Also, please check the DDL of role table(if exists)- datatype, width of column google_id and the mysql engine for both the tables.