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)
);
The table creation is successful but the foreign key is not listed.
What am I missing? This is really weird behavior that their is neither an error nor a warning shown by MySQL Workbench.
CodePudding user response:
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:
Error
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.