Can't set foreign key relationship

Question

I have some problem with MySQL Workbench in that I sometimes can't set foreign keys when creating tables. I say sometimes cause it's not always like this. The thing is when I enter a FK and choose a reference table I can't pick a referenced column. I can't click the check box and the drop down list is empty. I can't really figure out what the problem is cause I see no real difference from the FK's that are working. I have checked data type, name etc and they are correct. I'll provide a SS to elaborate. The green marked key (id_hem) is working ok and the red marks are those that don't.

Miscarriage

it has still the same problem after 7 years – Lurid

it has still the same problem after 11 years – Spier

Answer 1

I had the same problem and the issue was in foreign key indexes. MySQL workbench generates too long names for fk indexes sometimes. Manual correction helps.

Bijection

Could you please add some information on yhow to manually correct this? – Achernar

@Achernar Generate SQL script and then manually update too long index names in your favorite editor. – Bijection

Answer 2

I know this is old but the usual culprits are Non Null flag and Unsigned. Make sure you do match these on the foreign key column if the referenced column does have them enabled.

Altruistic

This was my problem. Thanks! Had set the NN and Unsigned on primary key but forgot on the foreign key before setting the constraint – Pledget

This is why I love SO. 3 years on and someone figures out what causes a bug that still hasnt been fixed and solves it! TY – Restore

Another reason could be different table collations. – Binghi

what a time saver! – Cazzie

Why do we need to match the Non Null flag? A foreign key column can be null right? This problem is occurring for me, but I don't want to make my foreign key column Non Null – Rosenkrantz

Didn't work for me. – Arduous

@R.S.K a foreign key guaranties data integrity across multiple tables so it has to have the exact constraints as the primary key it references – Altruistic

@Arduous maybe shahiin's comment above helps? – Altruistic

I don't understand why the Workbench doesn't provide a warning message for this mismatch and then load a prompt to align FK's flags to the PK it is pointing to (or vice-versa)? – Constantin

Answer 3

It can be also because of different collations. Just check if those 2 columns have the same collation.

In my case one was with

Table default (using utf8 general)

the other one with

utf8_unicode_ci

I have set those 2 columns with utf8_unicode_ci and it works.

George

Answer 4

I had the same problem.. then I have checked the two tables.. The issue was the type, it should be matched in both two tables.

if in foralda table column id_familjer is (INT)
in fkblixten table column id_familjer also should be be (INT). if the two data types are not matching MySQL Workbench won't allow to select the column as foreign key.

Furcula

Answer 5

In accepted answer Manual correction in the sense: First create a index for the referencing key in the referenced table. (you can do that in the indices tab available at each table). Then, after clicking the apply ( even without selecting referenced column) change the code in the apply accordingly.

example:

ALTER TABLE `SCHEMA_NAME`.`SECOND_TABLE_NAME` (we are creating foreign key in this second table)
ADD CONSTRAINT `FOREIGN_KEY_NAME`
  FOREIGN KEY (`COLUMN_IN_SECOND_TABLE`)
  REFERENCES `SCHEMA_NAME`.`FIRST_TABLE_NAME` (`COLUMN_IN_FIRST_TABLE`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Epithelioma

Yes, a script is what eventually worked for me and the easiest way to create one is to create a new model, recreate all tables in question and their relationships in it, then finally have workbench generate the script for you using forward engineering (just stop at the review script phase and pick up the SQL and clean it from the unnecessary stuff because it will also try to create a schema and all the mentioned tables). – Hellkite

This solution worked for me. Looks like DB Model is broken/outdated then? Any tips on how to regenerate this? – Arduous

Answer 6

Even though from table properties I haven't managed to change the foreign key value, I tried using SQL code and I did it.

Code:

ALTER TABLE Table2
ADD FOREIGN KEY (Table2_fk_field) REFERENCES Table1(Table1_pk_field);

Ardelia

Answer 7

id_familjer is a primary key? set it.
The Referenced Column only display the familjer table primary key.

Athanor

I just had this problem and it took far longer to solve than it should have... Even more than 10 years after you answered I'm still bumping my head into this - go figure! Thanks so much! – Underbelly

Answer 8

Foreign key should have same constants similar to Primary key. You can ignore AI ,Primary Key. If the Primary key has UI , foreign should also have UI

UI-> Unsigned Interger AI-> Auto Increment

Threaten

Answer 9

In my case, I change the 'collation' in every columns that I want to set the foreign keys.

For example, My FK column collation is 'utf8', I also change my reference table into 'utf8' too. And it's solved my problem.

I hope it's helpful.

Ejecta

Answer 10

Also to add my two cents, this also happens if you are making EER diagrams in the same database namespace and two or more diagrams contain a table with the same name.

So if you attempted to make a relation to a table (that also exists in another diagram with the same name) it will show up as twice in the reference table, both refer to your current diagram schema and it won't allow you to set a relation.

The solution is to rename the table in the other diagram.

Cagliostro

Answer 11

Make sure the data types are the same in both tables as if one says int and the other is int(6) it wont work as one has a limit. I had this problem and that is how I fixed it.

Damal

Answer 12

None of them worked... So I wrote the sql query myself in to set foreign key. It worked without any problem. After that, I could select the property (which I used in my query) in "Referenced Column" in right hand side.

Weird

Lurid

Answer 13

In my case, setting the constraint with existing data, I forgot to first check for and delete the rows in the child table that would violate the constraint (the foreign key was not found in the parent table)!

Tipstaff

Answer 14

I FOUND! If your refered table contains JSON field it's blocking ability to select columns from that table as foreign keys!!! workaround I switched those fields to TEXT and suddenly it worked!

Heterolecithal

Answer 15

I figured out a solution, i had my db set up like (table1 reference -> table2 reference -> table3). so there are multiple foregin keys. its strange but i got it working only when adding the relations from (table3 <- table2 <- table1), so just delete the foreign keys and add them from the ta

Piceous

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Makalu