This sounds like a stupid question, because there’s literally hundreds of tutorials out there.
But no matter which tutorial I follow to create a
foreign key or, in my case, a
composite foreign key, even though the table creation is always successful, MySQL Workbench does not show any
foreign key information.
If I create these 2 example tables
CREATE TABLE parent ( id INT NOT NULL, category VARCHAR(255) NOT NULL, PRIMARY KEY (id, category) ); CREATE TABLE child ( id INT PRIMARY KEY, category VARCHAR(255) NOT NULL, info TEXT, CONSTRAINT fk FOREIGN KEY (id, category) REFERENCES parent(id, category) );
What am I missing? This is really weird behavior that their is neither an error nor a warning shown by MySQL Workbench.
I tested your example tables in MySQL Workbench 8.0.28. It successfully created the
child table with its foreign key. I ran
SHOW CREATE TABLE child and the output shows the foreign key.
But MySQL Workbench apparently has a bug showing the foreign key in the visual table information. I viewed the foreign keys tab as you did. I pressed the "refresh" button and got this message:
Unhandled exception: invalid column constraint_name for resultset
Check the log for more details.
I checked the log (Help->Show Log File) and saw this:
10:15:53 [ERR][sqlide_tableman_ext.py:show_table:1186]: Error initializing tab constraints: Traceback (most recent call last): File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_tableman_ext.py", line 1183, in show_table tab.show_table(schema, table) File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_tableman_ext.py", line 854, in show_table self.refresh() File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_catalogman_ext.py", line 373, in refresh self.preload_data(self.get_query()) File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_catalogman_ext.py", line 367, in preload_data node.set_string(i, rset.stringFieldValueByName(field) or "" if format_func is None else format_func(rset.stringFieldValueByName(field))) SystemError: invalid column constraint_name for resultset
This bug was reported in February 2021: https://bugs.mysql.com/bug.php?id=102496 (and also the duplicate bug ysth links to in the comments above). The bug is "Verified" which means the MySQL team acknowledges it as a bug, but there is no fix and no estimate for when there will be a fix.
Answered By – Bill Karwin
Answer Checked By – Pedro (BugsFixing Volunteer)