Description:
MySQL Workbench version 8.0.33 build 2947366 CE (64 bits)
I have created a few tables with foreign keys. The foreign keys are created and I can verify them through the MySQL shell query
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>';
I can see the foreign key with autogenerated constraint names such as:
employee_ibfk_1, etc.
However, those foreign keys do not show up in the MySQL Workbench Table Inspector's Foreign keys tab. When I click Refresh in that tab the following error is in the workbench logs:
11:07:53 [ERR][sqlide_tableman_ext.py:show_table:1186]: Error initializing tab constraints: Traceback (most recent call last):
File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_tableman_ext.py", line 1183, in show_table
tab.show_table(schema, table)
File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_tableman_ext.py", line 854, in show_table
self.refresh()
File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_catalogman_ext.py", line 373, in refresh
self.preload_data(self.get_query())
File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\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
11:07:58 [ERR][ pymforms]: Unhandled exception in Python code:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\dsmel\AppData\Roaming\MySQL\Workbench\log\wb.log]
How to repeat:
Create a table with a self-referential foreign key as follows:
create table test_fk ( field1 varchar(15), field2 varchar(15) , primary key(field1), FOREIGN KEY(field2) REFERENCES test_fk(field1));
Verify that the FK constraint has been created
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'test_fk';
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| test_fk | field2 | test_fk_ibfk_1 | test_fk | field1 |
+------------+-------------+-----------------+-----------------------+------------------------+
In MySQL Workbench select this table, click on Table Inspector, select the Foreign keys tab (it will be empty), click refresh
Description: MySQL Workbench version 8.0.33 build 2947366 CE (64 bits) I have created a few tables with foreign keys. The foreign keys are created and I can verify them through the MySQL shell query SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '<database>' AND REFERENCED_TABLE_NAME = '<table>'; I can see the foreign key with autogenerated constraint names such as: employee_ibfk_1, etc. However, those foreign keys do not show up in the MySQL Workbench Table Inspector's Foreign keys tab. When I click Refresh in that tab the following error is in the workbench logs: 11:07:53 [ERR][sqlide_tableman_ext.py:show_table:1186]: Error initializing tab constraints: Traceback (most recent call last): File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_tableman_ext.py", line 1183, in show_table tab.show_table(schema, table) File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_tableman_ext.py", line 854, in show_table self.refresh() File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\sqlide_catalogman_ext.py", line 373, in refresh self.preload_data(self.get_query()) File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\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 11:07:58 [ERR][ pymforms]: Unhandled exception in Python code: ----[For better reports, please attach the log file after submitting. You can find it in C:\Users\dsmel\AppData\Roaming\MySQL\Workbench\log\wb.log] How to repeat: Create a table with a self-referential foreign key as follows: create table test_fk ( field1 varchar(15), field2 varchar(15) , primary key(field1), FOREIGN KEY(field2) REFERENCES test_fk(field1)); Verify that the FK constraint has been created SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'test_fk'; +------------+-------------+-----------------+-----------------------+------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +------------+-------------+-----------------+-----------------------+------------------------+ | test_fk | field2 | test_fk_ibfk_1 | test_fk | field1 | +------------+-------------+-----------------+-----------------------+------------------------+ In MySQL Workbench select this table, click on Table Inspector, select the Foreign keys tab (it will be empty), click refresh