Cannot create foreign key out of title columns in MySQL

Question

I am trying to create a foreign key.

Trying to Create Foreign Key

The columns in both tables are VARCHAR(255) and neither is a PK, however I keep getting this error message:

Foreing Key Error Message

I can’t figure out how to make a fk out of the topics title and the posts’s topic_title.

Thank you in advance for your help, much needed.

Have a very nice day, Ana

Please add the output from show create tables for both tables as text to the question. — P.Salmon, Apr 06 '21 at 14:41
Also note that sequel pro hasn't seen updates in years and is not really compatible with modern mysql anymore. — Evert, Apr 06 '21 at 14:42
You might like this checklist of foreign key errors: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Apr 06 '21 at 14:45
Thank you for your answer, do you have one to recommend for a mac osx? — ana_m, Apr 06 '21 at 14:49

score 2 · Answer 1 · answered Apr 06 '21 at 14:42

2

Only define foreign keys to primary keys. That is simply a best practice.

In order to add a foreign key in MySQL, though, you need to declare the column in the referred table as a primary key, as unique, or using an index. I strongly discourage you from using just any key. At the very least it should be unique. But preferably a primary key.

answered Apr 06 '21 at 14:42

Gordon Linoff

1,242,037
58
646
786

1

"But preferably a primary key". Could you give some reasons please ? (or links... ?) – St3an Apr 06 '21 at 14:45
_Only define foreign keys to primary keys_ Does that mean there should be only one foreign key in each table? Or does it mean that each foreign key should be a primary key in the referred table? – B001ᛦ Apr 06 '21 at 14:49
Excellent, thank you so much! I'll try that. Normally I wouldn't add a FK to a title, but I am using it on the client side in ReactJS as a key for a list, where my other foreign key wouldn't work. Thanks again! – ana_m Apr 06 '21 at 14:50
@B001ᛦ . . . No. That is referring to the *referenced* values. And any table should be referenced using only one key (or set of keys). Of course, the OP seems to. have a specific issue where referencing on a second, unique key might be appropriate. – Gordon Linoff Apr 06 '21 at 15:31
1

@St3an . . . Just Google the question. A primary key is *defined* as the column or columns that uniquely identify each row in a table. A foreign key is intended to refer to a row in a table. Voila. The one is made for the other. There are other reasons as well, but that should be sufficient to explain why primary keys -- which are explicitly defined to identify rows -- should be used for, well, identifying rows. – Gordon Linoff Apr 06 '21 at 19:03

Cannot create foreign key out of title columns in MySQL

1 Answers1