Question 20386 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Error 1822 Failed to add the Foreign Key Constraint – Mysql

I’m going over a coding assignment, and I’m running into an error I can’t solve.

I build a database and try to insert tables into it:

CREATE TABLE city(
city_id INT NOT NULL,
city_name VARCHAR(50) NOT NULL,
state VARCHAR(20),
zip CHAR(10) NOT NULL,
country VARCHAR(60) NOT NULL,
PRIMARY KEY (city_id)
);

CREATE TABLE users(
user_id INT NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
city VARCHAR(50),
state VARCHAR(20),
zip_code CHAR(10),
country VARCHAR(60),
phone VARCHAR(12),
email VARCHAR(30) NOT NULL,
user_password VARCHAR(25) NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY(city) REFERENCES city(city_name),
FOREIGN KEY(state) REFERENCES city(state),
FOREIGN KEY(zip_code) REFERENCES city(zip),
FOREIGN KEY(country) REFERENCES city(country)
);

Afterwards I get this error:
error 1822 failed to add a foreign key constraint. Missing index for constraint ‘users_ibfk_1’ in the referenced table ‘city’

I tried tweaking my nulls to align, and it still failed.
I attempted renaming

Tags:

2

Answers


  1. 0

    Your approach is wrong. Your error is caused by you having no indexes on the referenced columns, but that’s not the real problem here.

    Your underlying error is that you’re duplicating information in the City table into the Users table. This is an obvious place where dat acan become inconsistent.

    Instead, just store the city_id in the Users table, and use a foreign key to reference the city table. Do away with the duplicated columns, and use a JOIN to retrieve the data when you need it.

    CREATE TABLE city(
city_id INT NOT NULL,
city_name VARCHAR(50) NOT NULL,
state VARCHAR(20),
zip CHAR(10) NOT NULL,
country VARCHAR(60) NOT NULL,
PRIMARY KEY (city_id)
);

CREATE TABLE users(
user_id INT NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
city_id INT,
phone VARCHAR(12),
email VARCHAR(30) NOT NULL,
user_password VARCHAR(25) NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY(city_id) REFERENCES city(city_id)
);
Insert city (city_id,city_name, zip, country) values (1,'Vienna','90210','Austria'),(2,'Caracas','cx3045','Venezuela');
Insert users (user_id, first_name, last_name, email, city_id,user_password) values(1,'John','Doe','[email protected]',1,'HashedPassword');

    Then a JOIN like this:

    select users.first_name, users.last_name, city.city_name from users JOIN city using(city_id);

    Result:

    first_name last_name city_name
    John Doe Vienna

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    It usually indicates that there is an issue with the foreign key constraint you are trying to add to your table. The error message may provide additional information about the specific constraint that is causing the issue.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search