Failed to add the foreign key constraint

Question

I try to add a foreign key on a table but whenever I try I always have this error

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'total_ibfk_1' in the referenced table 'all_votes'

The table that is being created is this one (members is another table that has nothing to do with the error, so give no importance to it)

CREATE TABLE IF NOT EXISTS all_votes ( 
id_vote INT AUTO_INCREMENT PRIMARY KEY,
id_member INT,
name_member VARCHAR(20),
vote VARCHAR(20) NOT NULL,
type_of_vote VARCHAR(10) NOT NULL,
topic VARCHAR(20) NOT NULL,
FOREIGN KEY (id_member) REFERENCES members(id)
);

And this is the table that occur the error

CREATE TABLE IF NOT EXISTS total ( 
topic VARCHAR(20) PRIMARY KEY, 
type_of_vote VARCHAR(10) NOT NULL, 
counts INT NOT NULL, 
FOREIGN KEY (topic) REFERENCES all_votes(topic)
);

I've read a ton of answers, but none of them seems to work for me. This answer was the most instructive that summarize more less every other one.

I guess the error has to do with the reference of a foreign key to a non-primary key. The thing is that a lot of people says it's possible, but I didn't find a single code example, so I probably messed up somewhere there.

P.Salmon · Answer 1 · 2019-07-29T15:52:38.083

'MySQL requires indexes on foreign keys and referenced keys'https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html mysql will generate keys for you in the referencing table but YOU have to define them in the referenced table. in other words you need to add a key on topic in all_votes.

This syntaxes

CREATE TABLE IF NOT EXISTS all_votes ( 
id_vote INT AUTO_INCREMENT PRIMARY KEY,
id_member INT,
name_member VARCHAR(20),
vote VARCHAR(20) NOT NULL,
type_of_vote VARCHAR(10) NOT NULL,
topic VARCHAR(20) NOT NULL #,
#FOREIGN KEY (id_member) REFERENCES members(id)
);

alter table all_votes
    add key abk1(topic); 

CREATE TABLE IF NOT EXISTS total ( 
topic VARCHAR(20) PRIMARY KEY, 
type_of_vote VARCHAR(10) NOT NULL, 
counts INT NOT NULL,
FOREIGN KEY (topic) REFERENCES all_votes(topic)
    );

Note: I have commented out #FOREIGN KEY (id_member) REFERENCES members(id)

I changed a little bit the all_votes. Now primary keys are id_member AND topic, while id_vote is totally deleted. Even like this, the error is still there. Did I understand something wrong? Does it have to be the only primary key? — Dimitris Karamanis, Jul 29 '19 at 11:32

score 0 · Answer 2 · answered Jul 29 '19 at 11:05

0

because all_votes.topic column should be defined as PRIMARY OR UNIQUE KEY to be able to define a referenced foreign key for total.topic column.

answered Jul 29 '19 at 11:05

Barbaros Özhan

59,113
10
31
55

Failed to add the foreign key constraint

2 Answers2