-1

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

Anand Sundaram
  • 1
  • 1
  • Does this answer your question? [Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint](https://stackoverflow.com/questions/26329775/error-code-1822-failed-to-add-the-foreign-key-constaint-missing-index-for-con) – Phil Mar 21 '23 at 02:15
  • INDEX(city_name), INDEX(state), INDEX(zip), INDEX(country) put these four in city table – Indunil Jay Mar 21 '23 at 02:21
  • @IndunilJay No - that's not the solution. That involves a load of duplicated data and numerous unnecessary indexes. The real solution is to delete the columns from `Users` that duplicate the `City` data, store only the `city_id` in the `users` table and set a foreign key on that. Then use a JOIN to retrieve the data from both tables as needed. No duplicated data, and no additional indexes. See my answer. – Tangentially Perpendicular Mar 21 '23 at 02:36

2 Answers2

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','j.doe@example.com',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

Tangentially Perpendicular
  • 5,012
  • 4
  • 11
  • 29
-1

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.

Karina Thapa
  • 9
  • 1
    This is a barely relevant quote from somewhere. The error message the OP already has contains more useful information than this. ChatGPT? See https://meta.stackoverflow.com/q/421831/14853083 – Tangentially Perpendicular Mar 21 '23 at 02:41