search

dr5hn / countries-states-cities-database

🌍 Discover our global repository of countries, states, and cities! 🏙️ Get comprehensive data in JSON, SQL, PSQL, XML, YAML, and CSV formats. Access ISO2, ISO3 codes, country code, capital, native language, timezones (for countries), and more. #countries #states #cities
https://dr5hn.github.io/countries-states-cities-database/
Open Data Commons Open Database License v1.0
7.35k stars 2.56k forks source link

Foreign Key error #887

Open lionsgatecreative opened 1 week ago

lionsgatecreative commented 1 week ago

Attempting to execute the countries.sql file, but the following error is returned by phpMyAdmin:

1005 - Can't create table magelln_loc.countries (errno: 150 "Foreign key constraint is incorrectly formed")

dosubot[bot] commented 1 week ago

Hey @lionsgatecreative! I'm here to assist you with any bugs or questions you have while waiting for a human maintainer. Let's tackle this together!

The error you're encountering usually indicates a mismatch in the foreign key constraints. Here are a few things to check:

  1. Data Types: Ensure that the data types of the foreign key columns in the countries table match those in the referenced tables (regions and subregions). For example, region_id and subregion_id in countries should match the id in regions and subregions respectively. They should all be mediumint unsigned [1].

  2. Referenced Tables: Make sure the referenced tables (regions and subregions) exist and are created before the countries table.

  3. Indexes: Ensure that the referenced columns (id in regions and subregions) are indexed, as foreign keys require the referenced columns to be indexed.

  4. Character Set and Collation: Ensure that the character set and collation of the columns involved in the foreign key relationship are the same.

Double-check these aspects in your SQL file and the database setup.

To continue talking to Dosu, mention @dosu.

lionsgatecreative commented 1 week ago

Worked when I instead used world.sql.