Search code examples
mysqlsqlforeign-keysmysql-workbench

How to REALLY create a foreign key in MySQL?

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. enter image description here

What am I missing? This is really weird behavior that their is neither an error nor a warning shown by MySQL Workbench.

Solution

  • 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.