Cannot add a foreign key constraint MySQL

Question

I have a table named "countries" and another table named "country_continents" in my DB. I want to make my continent_id column in countries , a foreign key referencing the id of country_continents, but I am getting an error message. This is the SQL to create the foreign key and the error:

ALTER TABLE countries
    ADD CONSTRAINT fk_continent_id
    FOREIGN KEY (continent_id)
    REFERENCES country_continents(id);

ERROR: #1215 - cannot add foreign key constraint

At first, I was getting the :

"Error: relational features are disabled"

so I ran the command ALTER TABLE countries ENGINE=InnoDB; and ALTER TABLE country_continents ENGINE=InnoDB; but now I'm getting the #1215 error.

This is the Structure for "country_continents":

This is the Structure for "countries":

Any ideas on whats happening? Thanks in advance.

Are they the same data type ? Please share tables definitions. (https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — poleszcz, Sep 08 '21 at 10:52
@Akina the tables were created from migration in Laravel, so I do not have the CREATE TABLE. — mlila_p, Sep 08 '21 at 10:54
Execute `SHOW CREATE TABLE tablename;` and provide the output. — Akina, Sep 08 '21 at 10:54
Please publish the code for creating your tables `countries` and `country_continents` — Atika, Sep 08 '21 at 10:58
Just fixed the issue, thank you so much for all the responses. — mlila_p, Sep 08 '21 at 11:40

Atika · Accepted Answer · 2021-09-08T13:27:04.893

1

I think the error is due to:

in your countries table, continent_id is not unsigned. Edit that and tell me if it worked

edited Sep 08 '21 at 13:27

answered Sep 08 '21 at 11:40

Atika

1,025
2
6
17

Cannot add a foreign key constraint MySQL

1 Answers1